COUNTIFS OR COUNTIFS needs to work

**joshbellfi** · 05-23-2013, 10:21 AM

Hello,

Currently I need

=COUNTIFS(B:B, "WA",D:D, "2006",A:A, "Bellingham")+COUNTIFS(B:B, "WA",D:D, "2006",A:A, "Anacortes ")

to work. Basically It needs to recognize if it's from 2006 and this state and if it's either one of these two cities.

I'm stuck and thought the above function would work, but it doesn't.

Help would be be very much appreciated.

**Fotis1991** · 05-23-2013, 10:24 AM

Perhaps

=SUMPRODUCT(COUNTIFS(B:B, "WA",D:D, "2006",A:A,{ "Bellingham","Anacortes "}))

**dluhut** · 05-23-2013, 10:25 AM

instead of "WA" or "2006" etc, use "=WA" and "=2006"...YES, INCLUDING the Quotation

**joshbellfi** · 05-23-2013, 10:42 AM

Thanks you guys!

**Tony Valko** · 05-23-2013, 10:55 AM

If you're going to hardcode the criteria:

=SUM(COUNTIFS(B:B,"WA",D:D,2006,A:A,{"Bellingham","Anacortes"}))

If you use cells to hold the criteria:

F2 = WA
G2 = 2006
H2 = Bellingham
H3 = Anacortes

=SUMPRODUCT(COUNTIFS(B:B,F2,D:D,G2,A:A,H2:H3))