Counting the number of entries with 2 criteria

**McQLon** · 02-11-2009, 08:48 AM

Hi,

I want to pick out the number of "Current" employees for Group A and the number of "Current" employees from Group B. Can anyone suggest a formula which would do this?

I attach an example of what I use and how I want the results to appear.

Many thanks in advance for any help with this.
McQLon

**DonkeyOte** · 02-11-2009, 08:54 AM

Perhaps:

Please Login or Register  to view this content.

Though it would be better to place:

Please Login or Register  to view this content.

THen you can use a SUMIF (more efficient)

Please Login or Register  to view this content.

**NBVC** · 02-11-2009, 08:54 AM

Try:

=SUMPRODUCT(--($B$10:$B$22=$B4),--($C$10:$C$22="Current"))

copied down to B5

**sweep** · 02-11-2009, 08:55 AM

Hi,

In C4 and C5 ,

=SUMPRODUCT(($B$10:$B$22=B4)*($C$10:$C$22="Current"))
=SUMPRODUCT(($B$10:$B$22=B5)*($C$10:$C$22="Current"))

**sweep** · 02-11-2009, 08:59 AM

@DonkeyOte and NVBC,

We all quite often answer threads requiring a SUMPRODUCT answer. I'm just curious is there any benifit in using =SUMPRODUCT(--(array1),--(array2)) over =SUMPRODUCT((array1)*(array2)) ?

**DonkeyOte** · 02-11-2009, 09:06 AM

There are a few reasons IMO for using double unary over multiplication:

a) -- is regarded as quicker (slightly)
(some argue for single unary approach but that brings with it other risks)

b) -- method allows for non-numerics in summation range (if used)

consider

A1 & A2 = "a"
B1 & B2 = "b" & 10 respectively

=SUMPRODUCT((A1:A2="a")*(B1:B2)) --> #VALUE!
=SUMPRODUCT(--(A1:A2="a"),B1:B2) --> 10

If you've not already have a read of Bob's page: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

I have other links on the topic of coercion if interested.

**McQLon** · 02-11-2009, 09:09 AM

It's working fine with those formulas! Thanks!

In the end I named the ranges and used Countif.

Thanks again!

**sweep** · 02-11-2009, 09:13 AM

I see, thanks.

In my finite world the speed of the function isn't going to count for much, so I think that the "non numerics in a summation range" is probably the best arguement for using double unary. Thanks for the link, and the explaination.

Dave

**DonkeyOte** · 02-11-2009, 09:16 AM

It's interesting that here at ExcelForum.com the more common approach is Sumproduct by * whereas over at MrExcel.com the double unary approach is the norm... in truth it's a case of "horses for courses" but in the majority of cases I honestly believe double unary to be the better more flexible of the approaches.

HOWEVER...

The double unary approach IS restricted insofar as each range must be of equal dimension...

Assume A1:A2 holds criteria and B1:Z2 hold numerical value

=SUMPRODUCT(--(A1:A2="a"),B1:Z2)

will NOT work... you must use:

=SUMPRODUCT((A1:A2="a")*(B1:Z2))

However, if you had criteria in A1:B2 and values in C1:D2 you could use --

=SUMPRODUCT(--(A1:B2="a"),C1:D2)

because both ranges are 2 x 2