Countifs with Multiple Criteria

[BillHassall]

I am trying to use Countifs to count the same set of cells but with different criteria either by directly inputiing the names that I want to find (H5, H6 and H7, this is text and not cell number) or by reference to the cells that the information is in (cells D1, D2 & D3), so what I want to achieve is one of the following

COUNTIFS(September!$G:$G,{"H5";"H6";"H7"},September!$B:$B,Totals!B$17)

This only gives cells that have H5 in them not H6 or H7

My preferred method would be as follows which doesnt work at all

COUNTIFS(September!$G:$G,D1 or D2 or D3,September!$B:$B,Totals!B$17)

In the second case cells D1,D2 & D3 would contain the text H5, H6 & H7

Thanks

Bill

 

[RoryA]

Try:
=SUM(COUNTIFS(September!$G:$G,{"H5";"H6";"H7"},September!$B:$B,Totals!B$17))

 

[barry houdini]

To use the cell references

=SUMPRODUCT(COUNTIFS(September!$G:$G,D1:D3,September!$B:$B,Totals!B$17))

 

[BillHassall]

Excellent, both work a treat, thanks for your help, spent ages trying to find the answer before posting

Bill