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"))

[chrisdedobb]

Thanks! That works great!

[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.

chrisdedobb wrote:
>
> 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?
>
> --
> chrisdedobb
> ------------------------------------------------------------------------
> chrisdedobb's Profile: http://www.excelforum.com/member.php...o&userid=30081
> View this thread: http://www.excelforum.com/showthread...hreadid=497615

--

Dave Peterson

[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]...
>
> 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?
>
>
> --
> chrisdedobb
> ------------------------------------------------------------------------
> chrisdedobb's Profile:
http://www.excelforum.com/member.php...o&userid=30081
> View this thread: http://www.excelforum.com/showthread...hreadid=497615
>

[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.