# count if with multiple conditions

#### Kmcbrine

##### New Member
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!

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Hi,

Try:

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

Countif with multiple conditions

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

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

Should be a : not a - between the range

Should be a : not a - between the range

COUNT with multiple conditions

Thanks. Tried this, still no go?

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

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

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

This worked when I tested it!

Michael

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.

count with multiple conditions

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?

Replies
1
Views
107
Replies
4
Views
522
Replies
3
Views
141
Replies
3
Views
202
Replies
2
Views
132

1,203,468
Messages
6,055,599
Members
444,801
Latest member
JurieDT

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back