Counting the number of times more than 1 variable occurs

[chrisdedobb]

I need to determine how many Separations were processed by a particular
salesperson.

A | B
1 MIKE | SEPARATION
2 MIKE | LEAVE
3 SARA | SEPARATION
4 JOE | SEPARATION
5 JAMIE | LEAVE
6 MIKE | LEAVE
7 JOE | LEAVE
8 SARA | SEPARATION

The only way I can think of is the COUNTIF Funtion
*COUNTIF(-range,criteria-)*
=COUNTIF(A1:A8,(A1:A8="MIKE")*(B1:B8="SEPARATION"))
This does not work though. The results return 0 with no errors when
the answer should be 1.

does anyone else know of a way to get what I need? Or is there a
problem with my formula?

 

[Vito]

Try

=sumproduct(--(a1:a8="Mike"),--(b1:b8="Separation"))

 

[Dave Peterson]

You may want to look at pivottables. You won't have to build separate formulas
for each name.

but:

=sumproduct(--(a1:a10="mike"),--(b1:b10="Separation"))

Adjust your ranges, but don't use the whole column.

=sumproduct() likes to work with numbers.

The -- converts trues and falses to 1's and 0's.

 

[Bob Phillips]

=SUMPRODUCT(--(A1:A8="MIKE"),--(B1:B8="SEPARATION"))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"chrisdedobb" <[email protected]>
wrote in message
news:[email protected]...

 

[chrisdedobb]

Thanks! That works great!

 

[BenjieLop]

I need to determine how many Separations were processed by a particular
salesperson.

A | B
1 MIKE | SEPARATION
2 MIKE | LEAVE
3 SARA | SEPARATION
4 JOE | SEPARATION
5 JAMIE | LEAVE
6 MIKE | LEAVE
7 JOE | LEAVE
8 SARA | SEPARATION

The only way I can think of is the COUNTIF Funtion
*COUNTIF(-range,criteria-)*
=COUNTIF(A1:A8,(A1:A8="MIKE")*(B1:B8="SEPARATION"))
This does not work though. The results return 0 with no errors when
the answer should be 1.

does anyone else know of a way to get what I need? Or is there a
problem with my formula?

=SUMPRODUCT((A1:A8=\"MIKE\")*(B1:B8=\"SEPARATION\"))

is the formula to use.

Regards.