Formula

[vincentharris]

I have a spreadhseet with two tabs. One tab has all of the details. The second tab has a summary of the first tab. I have a formula as follows:=COUNTIF('DETAILED LIST'!$M:$M,"Ticket_"&TEXT(SUMMARY!$A7,"MM/DD/YYYY"))

This formula is counting and summarizing TICKET that is listed on the first tab. Now other options have been added like PROJECT, IMAC and BREAK/FIX. How do I included those new options in my current formula; :=COUNTIF('DETAILED LIST'!$M:$M,"Ticket_"&TEXT(SUMMARY!$A7,"MM/DD/YYYY"))
That will sum up on TICKETS, BREAK/FIX, IMAC and PROJECT?

 

[GlennUK]

Without seeing your data I can only guess, but, maybe something like:

=COUNTIF('DETAILED LIST'!$M:$M,"Ticket_"&TEXT(SUMMARY!$A7,"MM/DD/YYYY"))+COUNTIF('DETAILED LIST'!$M:$M,"Break/Fix_"&TEXT(SUMMARY!$A7,"MM/DD/YYYY"))+COUNTIF('DETAILED LIST'!$M:$M,"IMAC_"&TEXT(SUMMARY!$A7,"MM/DD/YYYY"))+COUNTIF('DETAILED LIST'!$M:$M,"Project_"&TEXT(SUMMARY!$A7,"MM/DD/YYYY"))

... although you start out by saying that the summary is counting, and finish by saying that you want things summed up?!?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[vincentharris]

Glenn:

This is what I needed but I do have one question....Once I put the formula in it brings me ot it ask me if I want to box that says update Values: Summary. Am I suppose to click yes to update it?

 

[vincentharris]

Glenn

Are you sure I can use that entire formula?

 

[AnotherHiggins]

Without seeing your data I can only guess

How about a sample of what your data looks like?

.... sum up on TICKETS, BREAK/FIX, IMAC and PROJECT

First I'd point out that your formula doesn't seem to sum anything, but rather count the number of times a criteria is met.

Regardless, your quote above sounds to me like you want to count the number of records on the detail tab that meet conditions in multiple columns. Is that right? If so, I'd suggest you look at SumProduct. Depending on how it's used, it can return either sums or counts.

But before putting together an example, I'd like confirmation on what you're trying to do.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[GSCaupling]

Hi vincentharris,

Welcome to Tek-Tips.

I wonder if a PivotTable would solve your problem. Set the data summary to count and you will have a convenient recap of TICKETS, BREAK/FIX, IMAC and PROJECT individually (and with a grand total for all of them) and by date. Further, you can group the dates for monthly summaries, etc.

Hope this helps!
GS

[Green]******^*******
[small]I[/small] [small]Hate[/small] [♥] [small]Ambiguity.[/small][/green]

 

[vincentharris]

This is my formula

=COUNTIF('DETAILED LIST'!$M:$M,"Incident_"&TEXT(SUMMARY!$A7,"MM/DD/YYYY"))+COUNTIF('DETAILED LIST'!$M:$M,"Ticket_"&TEXT(SUMMARY!$A7,"MM/DD/YYYY"))+COUNTIF('DETAILED LIST'!$M:$M,"Project_"&TEXT(SUMMARY!$A7,"MM/DD/YYYY"))+COUNTIF('DETAILED LIST'!$M:$M,"Break/Fix_"&TEXT(SUMMARY!$A7,"MM/DD/YYYY"))+COUNTIF('DETAILED LIST'!$M:$M,"IMAC_"&TEXT(SUMMARY!$A7,"MM/DD/YYYY"))

I have to type this into every cell I am using becuase if I type it altogether a popup box ask me if I want to save it somewhere

 

[kjv1611]

vincentharris,

Have you tried the pivot table, as GSCaupling mentioned? Pivot tables are much easier to set up and change as needed than long complex formulas when they are feasible.

--

"If to err is human, then I must be some kind of human!" -Me

 

[AnotherHiggins]

My crystal ball is hazy.

I'm really trying to be helpful here, and I've got what I think you're after, but you still haven't provided what's been asked for twice: some sample data!

It look like you're just looking for anything in Column M of the Detail sheet that contains the date in a particular cell on the summary sheet. So why not just generalize with wildcards?
[tab][COLOR=blue white]=CountIf('DETAILED LIST'!M:M, "*" & TEXT(SUMMARY!A7, "MM/DD/YYYY") & "*")[/color]

I have to type this into every cell I am using becuase if I type it altogether a popup box ask me if I want to save it somewhere

I don't know what that's supposed to mean. Giving us clues like ERROR MESSAGES would be a good way to receive help in troubleshooting.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.