count if with multiple conditions

[Kmcbrine]

Good Morning,

I'm trying to:

If D1:d5 is Y, then count the number of cells in f1:f5 that contain 5.

IF possible, I would like to be able to say <5

Does it matter if the cells are formated as text or number?

Thanks very much, appreciate your help!

 

[fairwinds]

Hi,

Try:

=SUMPRODUCT(--(D1:D5="Y"),--(F1:F5<5))

 

[Kmcbrine]

Countif with multiple conditions

=SUMPRODUCT(--(D2-D106="Y"),--(F2-F106<=5))

Tried this, I get a #VALUE! msg. See any blaring errors?

 

[hedgie]

Should be a : not a - between the range

 

[hedgie]

Should be a : not a - between the range

 

[Kmcbrine]

COUNT with multiple conditions

Thanks. Tried this, still no go?

=SUMPRODUCT(--(D2:D106="Y"),--(F2-F106<=5))

 

[hedgie]

still left it out in second part of the equation
Try:
SUMPRODUCT(--(D2:D106="Y"),--(F2:F106<=5))

 

[daniels012]

Try this:
=IF(COUNTIF(D1:D5,"Y"),COUNTIF(F1:F5,5),"")

This worked when I tested it!

Michael

 

[fairwinds]

Kmcbrine:

Just copy the formula as I posted it into your sheet. Then adjust the ranges.


daniels012:

That would count all the 5's as soon as you havee one Y.

 

[Kmcbrine]

count with multiple conditions

First, thank you for your patients, I admit I'm an idiot.

I did try both formulas, and they are both valid, but returning 0, when I should have a result of 2.

Any chance I could mail the spreadsheet to you and see what's going on?

Does it matter if I have blank cells?

Does it matter if the column is formatted as text or a number?