[Solved] Count if multiple Conditions met in Multiple ranges

[JoeyDragonSales]

Hi, I am a sales specialist working leads received by phone as well as internet leads. When I sell someone, I record Phone lead(ph) or Internet lead(int). In column A range a10:a65 I I record the customers name in each Cell. Range b10:65 is Ph or int. Range D10:D65 I Record "Yes" or "No" in the sold section.

I'd like to be able to record in Cell h2 the number of leads that have "Ph" in range B10:B65 AND "Yes" in Range D10:65.

In cell I2 I'd like to record the Numer of leads that have "Int" in range B10:B65 AND "Yes" in Range D10:D65.

I have tried =SUM((B10:B65="ph")*(D10:D65="yes")) but it will only work in h2, I cannot duplicate it into I2 and simply Change "ph" to "int" in the formula.

I am lost.. Please help.

[karolus]

Hallo
use

Code: Select all

=SUMPRODUCT($B$10:$B$65="ph";$D$10:$D$65="yes")

Karolus

[JoeyDragonSales]

Karolus thank you for your reply, I tried that, tried with and without the $ sign. Didn't work.

[RoryOF]

Did you try whatever variation of "ph" "PH" etc you used on the main form?

[JoeyDragonSales]

Yes. I used lower case everything to initially write the formula set, but I cant seem to figure out this multi Condition thing.

[JoeyDragonSales]

I've used =COUNTIF(D10:D65;"=Ph") for a running phone tally. Now If I could figure out how to add another condition to that I'd have exactly what I need

[Villeroy]

SUMPRODUCT works for everyone but you.
SUMIF/COUNTIF works with one criterion only.

[thinman3]

Attached is an example using your data scheme.

Ph, Int --SUMPRODUCT.ods

(7.72 KiB) Downloaded 1050 times

thinman3

[JoeyDragonSales]

Thinman3,
That worked perfectly. Thank you very much.