CountA or countblank with IF

[rcarlson67]

I have a sheet with Name, Shift, and four columns for four classes. The four classes just have dates entered for the date each person completed the class. Shifts are "Days", "Swings", "Graves". In my example I have 5 people, 2 on days, 1 on swings, 2 on graves. (see pic). I want to COUNTA or COUNTBLANK the entire range of classes and dates only column(Shift) equals say "Days". So for this example I have to people on Days. One has 2 of 4 classes completed, one has 1 of four classes completed. I'm looking for a total of 3. I'm looking to count from the range of all the classes for all the staff in case someone sorts by name or shift, so I don't want a static range. I started out with

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

I either get a "False" or the total number of the range E10:H14 when I only want to count the rows that have a specific Shift name.

[rcarlson67]

Sheet1.jpg the attached example

[daddylonglegs]

Try this formula

=SUMPRODUCT((D10:D100="Days")*ISNUMBER(E10:H100))

[alvin-chung]

Hi rcarlson67, I'll probably use SUMPRODUCT for a start.
Could you upload a sample spreadsheet instead?



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[rcarlson67]

Thank you Daddylonglegs that is exactly what I was looking for! In case anyone is interested I also need the empty cells, changing "ISNUMBER" with "ISBLANK" counted the empty cells.