Formula to calculate total from column with text

**Sadalif** · 07-26-2013, 11:07 AM

Can someone help with a formula - I already have one which was done by someone else, I need to amend and was hoping there was a simpler way..

=SUM(IF('TEST'!$I$7:$I$10898>0,IF('TEST'!$D$7:$D$10898=$C6,1,0),0),0)

I have 80 different areas and want to be able to calculate how many people are down to attend from each area. My areas are in text format..

I also need to calculate in 3 other columns the amount of no shows for each area, I have 3 date options so its no shows for each date for each area?

Any help will be appreciated

Thanks

**daddylonglegs** · 07-26-2013, 11:13 AM

So C6 is a specific area and 'TEST'!$D$7:$D$10898 contains areas? What's in 'TEST'!$I$7:$I$10898 and how do you determine a "no show"?

**Sadalif** · 07-26-2013, 11:27 AM

C6 isnt really relevant, just titles
D7:D10898 is the areas where attendees are from
I7:I10898 is the first date column (I has day 1 dates, K day 2, M day 3), a no show has an N, attendance is Y

**daddylonglegs** · 07-26-2013, 01:52 PM

OK so for No shows for date 1 for an area shown in Z2 (adjust as required) try

=COUNTIFS('TEST'!$J:$J,"No",'TEST'!$D:$D,Z2)

I assume you won't have to test whether there's a date in column I, presumably there won't be a "No" in J if there isn't a date in that column?