Should this work? =SUMPRODUCT( --($K$3:$K$4650&D3),...

[pgarcia]

=SUMPRODUCT( --($K$3:$K$4650&D3), --($L$3:$L$4650&$F$2), --($M$3:$M$4650))

or

=SUMPRODUCT( --($K$3:$K$4650&D3), --($L$3:$L$4650&$F$2), ($M$3:$M$4650))

or

=SUMPRODUCT( --($K$3:$K$4650=D3), --($L$3:$L$4650=$F$2), --($M$3:$M$4650))

 

[pgarcia]

The data is like so:
K L M
ORG Reason #1
ABQ MC 1
ABQ MC 1
ABQ MC 1
ABQ MC MD 1
ABQ MC MD 1
ABQ MC MD 1
ABQ NO REV CONS 1
ABQ NO REV CONS 1
ABQ NO REV CONS 1
ALB MC 1
ALB MC 1
ALB MC MD 1
ALB MC MD 1
ALB NO CONS 1
ALB NO REV CONS 1
ALB NO REV CONS 1

 

[Farhad]

Hi,

Please let us know what you want to do? Non of the formulas are correct.

Thanks,

 

[pgarcia]

Sorry, I posted the data right after I post this question. I'm trying to find
out how many ABQ has of MC. In the below example ABQ has 3 MC. Does that make
since?
And thanks for the reply.

The data is like so:
K L M
ORG Reason #1
ABQ MC 1
ABQ MC 1
ABQ MC 1
ABQ MC MD 1
ABQ MC MD 1
ABQ MC MD 1
ABQ NO REV CONS 1
ABQ NO REV CONS 1
ABQ NO REV CONS 1
ALB MC 1
ALB MC 1
ALB MC MD 1
ALB MC MD 1
ALB NO CONS 1
ALB NO REV CONS 1
ALB NO REV CONS 1

 

[Sandy Mann]

Why not just try them?

The third one works but why are all the ranges absolute except D3?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[JP]

Here's an array formula that should work.

=COUNT((K3:K4650="ABQ")*(L3:L4650="MC")*(M3:M4650))

Ctrl-Shift-Enter

This will return the number of times "ABQ" and "MC" appear in the same
row.

Adapted from http://www.cpearson.com/excel/ArrayFormulas.aspx

HTH,
JP

 

[pgarcia]

AHHHHHHH!!!! Ok, I didn't check the data. As I was looking at it, in D3
(which is ABQ, ect.) there was space after the last letter. So doing it
worked: =left(D3,3). What a NewB move. Thanks. After I read your comment it
just came to me.

 

[pgarcia]

Read the post to Sandy Mann.

Thanks any ways.

Hi,

Please let us know what you want to do? Non of the formulas are correct.

Thanks,

 

[Farhad]

Hi,

Try this:

=SUMPRODUCT( --($K$3:$K$4650="ABQ"), --($L$3:$L$4650="MC"))