# Is this formula the best option?

#### pawebb

##### Board Regular
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
ABCDE
1260_6537PROMOTIONAL260_6553SHORTLEATHER260_6567SUEDE262_6474FAUXFUR/PLUSH
2121212
3121210
41366
58888
6111210
711119
8121010
9121212
105455
11Classes
1311000
1420000
1530100
1640100
1751011
1860011
1970000
2081111
2190010
22100130
23112100
24124420
Sheet1

Thanks...

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### Lewiy

##### Well-known Member
Try this in cell B13 and copy across all the other cells:
Code:
``=SUMPRODUCT(--(B\$2:B\$10=\$A13),--(B\$2:B\$10<>""))``

#### pawebb

##### Board Regular
Try this in cell B13 and copy across all the other cells:
Code:
``=SUMPRODUCT(--(B\$2:B\$10=\$A13),--(B\$2:B\$10<>""))``

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

#### Lewiy

##### Well-known Member
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

##### MrExcel MVP
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

##### MrExcel MVP
Hi

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

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

Hope this helps
PGC

##### MrExcel MVP
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.

Replies
0
Views
124
Replies
2
Views
338
Replies
8
Views
212
Replies
2
Views
139
Replies
1
Views
124

1,191,036
Messages
5,984,273
Members
439,881
Latest member
Amitoj95

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back