Subtotal with Countifs

[desert rat]

Hi All,

I have a training spreadsheet where it counts those that have completed, not completed or are exempt from the training. See attached spreadsheet.

What I would like to be able to do is filter by the Business Unit to see who has completed, not completed or is exempt.

Without filtering the Countifs formula works:
COUNTIFS(B6:B19,"<>exempt",B6:B19,"<>")

But when adding in the Subtotal function I continually get errors. I have tried many different versions of the below formula with no luck :
SUMPRODUCT(SUBTOTAL(103,OFFSET(F6,ROW(F6:F282)-ROW(F6),0)),(F6:F282,"<>exempt",F6:F282,"<>"))

Any ideas would be great.

Thanks

 

[NARAYANK991]

Hi ,

See if this is what you wanted.

https://www.extendoffice.com/documents/excel/2519-excel-countif-filtered-data.html#functions

Narayan

 

[desert rat]

Hi ,

See if this is what you wanted.

https://www.extendoffice.com/documents/excel/2519-excel-countif-filtered-data.html#functions

Narayan

Thanks Narayan,

That formula works but when entered it only counts the exempt cells. For the Total in column E1 I would like to only count the Completed and Non completed.

Any other ideas?

Thanks

 

[desert rat]

Ok I have come up with a work around and just had the Total count all text containing "Completed"
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B6:B19,ROW(B6:B19)-MIN(ROW(B6:B19)),,1)),ISNUMBER(SEARCH("Completed",B6:B19))+0)

Then for Specifically identifying those that are "Completed" and "Not Completed" I went with:
=SUMPRODUCT((B6:B19="Completed")*SUBTOTAL(103,OFFSET(B6,ROW(B6:B19)-MIN(ROW(B6:B19)),0)))
and
=SUMPRODUCT((B6:B19="Not Completed")*SUBTOTAL(103,OFFSET(B6,ROW(B6:B19)-MIN(ROW(B6:B19)),0)))

Might not be the neatest way to do it but it seems to be functioning for now.

Thanks