Is this formula the best option?

[pawebb]

I tried to create a pivot table to get my answer but couldn't figure out a clean way to do it. So I can up with the formula in the example. I wanted to see if there is a more efficient way to go about it. I need to count the number of times a store grade appears under a class.

Book1
	A	B	C	D	E
1		260_6537PROMOTIONAL	260_6553SHORTLEATHER	260_6567SUEDE	262_6474FAUXFUR/PLUSH
2		12	12	12
3		12	12	10
4		1	3	6	6
5		8	8	8	8
6		11	12	10
7		11	11	9
8		12	10	10
9		12	12	12
10		5	4	5	5
11		Classes
12	StoreGrades	260_6537PROMOTIONAL	260_6553SHORTLEATHER	260_6567SUEDE	262_6474FAUXFUR/PLUSH
13	1	1	0	0	0
14	2	0	0	0	0
15	3	0	1	0	0
16	4	0	1	0	0
17	5	1	0	1	1
18	6	0	0	1	1
19	7	0	0	0	0
20	8	1	1	1	1
21	9	0	0	1	0
22	10	0	1	3	0
23	11	2	1	0	0
24	12	4	4	2	0
Sheet1

Thanks...

 

[Lewiy]

Try this in cell B13 and copy across all the other cells:

=SUMPRODUCT(--(B$2:B$10=$A13),--(B$2:B$10<>""))

 

[pawebb]

Try this in cell B13 and copy across all the other cells:

=SUMPRODUCT(--(B$2:B$10=$A13),--(B$2:B$10<>""))

It works great but is there a benefit to either way?

 

[Lewiy]

I’m not entirely sure to be honest. I personally prefer to avoid using array formulas wherever possible because I find them less obvious to correct. However, there are times when they are the only/best solution. For your basic counting or summing exercises, I usually side with SUMPRODUCT because it is very versatile and generally quite clear to understand what you are trying to do when looking at it.

 

[Jonmo1]

It works great but is there a benefit to either way?

Generally speaking, NON array formulas perform faster than array formulas (CTRL + SHIFT + ENTER)

Plus, it's easy to forget to use CTRL + SHIFT + ENTER. If you've had the array formula working for a while, and need to make an adjustment to it, you might forget that it's an array formula and begin wondering why it doesn't work anymore...

 

[pgc01]

Hi

In your example you could also use in B13, copy down and accross:

=COUNTIF(B$2:B$10,$A13)

Hope this helps
PGC

 

[Aladin Akyurek]

B3, copy across and down:

=COUNTIF(B$2:B$10,$A13)

Some remarks:

{=COUNT(IF(B$2:B$10=$A13,B$2:B$10))}

is expensive and not needed for a single condition.

The above applies to:

=SUMPRODUCT(--(B$2:B$10=$A13),--(B$2:B$10<>""))

Also, if one understands the "array formulas", SumProduct formulas are mutatis mutandis in the closure of that understanding. The reverse is also true.

BTW, the COUNTIF formula is faster than the others.