Count If

[glinboy]

Hi,
I'm trying to write a formula to count the number of particular stock items available in stores in different states. I've started writing a COUNTIFS formula which using the example below counts the number based on supplier and dept =COUNTIFS(B2:B404,R1,C2:C404,S1,??)
but i'm getting stuck on how to finish the formula because the numbers for each state are in different columns, if that makes sense??!

Supplier	Department	Brand	Line	NSW	NT	QLD	SA	TAS	VIC	WA
x	x	x	x	1​	1​	1​	1​	1​	1​	1​

 

[jasonb75]

Looking at it, I would guess that you you want sum rather than count? (A count would tell you have many states have the item, a sum would tell you have many items they have in total).

Because the last part is spread over several columns, you will need sumproduct to do this.
Sum formula

=SUMPRODUCT(($B$2:$B$404=$R1)*($C$2:$C$404=$S1),$E$2:$K$404)

Count formula

=SUMPRODUCT(($B$2:$B$404=$R1)*($C$2:$C$404=$S1)*($E$2:$K$404<>0))

 

[glinboy]

thanks (again) Jason! yes, you are correct, will need the sum formula, the only issue i can see with the formula above is that i need to sum the stock items for different stores, which are located in different states, so I need to add in my formula a part which says that if the store is located in WA as shown in the example below, it looks up the WA column in the data example shown above?

		Manufacturers
	StoreName	x	x	x
WA	Alexander Heights	?

 

[jasonb75]

I believe that this should do it, you might need to adjust it slightly. I've used "WA" instead of a cell reference in order to make it stand out, but either can be used.

=SUMPRODUCT(($B$2:$B$404=$R1)*($C$2:$C$404=$S1)*($E$1:$K$1="WA"),$E$2:$K$404)

You may need to change the comma near the end to an asterisk, I've left the comma to avoid errors if the table includes formula blanks.
AFK for rest of the day, hopefully someone else will check your thread, I'll look at it when I can later.

 

[glinboy]

brilliant Jason, thanks again for all your help