# COUNTIF (2) Conditions

#### Featherk

##### New Member
I'm trying to count with (2) conditions: and I'm using COUNTIF
Example
Column A has 450 lines items and each line item is assigned to various people

Column B has various Delta's for days late. (i.e. <10 days late, <30 Days late, etc

My first Count I can get, My formula is COUNTIF (I1:I450, "<10") = 100

Now I want to count of the 100 how many are Jane Doe

I've tried this COUNTIF (B1:B450, "<10") and, COUNTIF (A1:A450, "<Jan Doe") - this does not work

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
I'm trying to count with (2) conditions: and I'm using COUNTIF
Example
Column A has 450 lines items and each line item is assigned to various people

Column B has various Delta's for days late. (i.e. <10 days late, <30 Days late, etc

My first Count I can get, My formula is COUNTIF (I1:I450, "<10") = 100

Now I want to count of the 100 how many are Jane Doe

I've tried this COUNTIF (B1:B450, "<10") and, COUNTIF (A1:A450, "<Jan Doe") - this does not work

Try:

=Sumproduct(--(B1:B450<10),--(A1:A450="Jane Doe"))

EDIT: Forgot the quotations around "Jane Doe"

Welcome to the board

Try

=SUMPRODUCT(--(B1:B450<10),--(A1:A450="Jane Doe"))

I missed the quotes in my typing actually had them in my formula.... but sumproduct is not working either. I get no results, any other suggestions.

What exactly is in column B? Just numbers or numbers with a < symbol in front?

If they are numbers, then either they are formatted as text or the strings in column A have extra spaces or you mispelled?

What result do you actually get? A 0 or an error.

Also, please show the actual formula you are using now.

Yes you are correct with the formatting but I can't figure it out. My people are not names, they are numbers.... i.e. instead of "Jane Doe" I'm using:

"143.1"
"143.2"
"143.3"

My formatting is "General". If I don't change the format but change the number to "Jane Doe" I get the correct results. Getting closer, so how do I fix this if I need to use "143.1", etc.

If they are actual numbers remove the quotes and use the number:

=SUMPRODUCT(--(B1:B450<10),--(A1:A450=143.1))

O.K. still not getting correct data. I'm actually getting some numbers populated but they aren't correct.

For Less than 10 days late I should have 37 but I'm only getting 28. Not sure what that means but I also can't imagine anyone figuring this one out.

If someone has any addtional thoughts - I'd appreciate it. Here's my formula right now, that I'm getting incorrect data.
=SUMPRODUCT(--('Rescheduled Master Listing'!\$I\$8:\$I\$446<10),--('Rescheduled Master Listing'!\$C\$8:\$C\$446=125.1))

Column I is a delta # provided by subtracting (2) dates.

=DAYS360(G305,H305)

Column C - is strictly typed in number

If your numbers in column C are formatted to show 1 decimal place then you may miss some "matches" if the value is something like 125.10001. You may want to try

=SUMPRODUCT(--('Rescheduled Master Listing'!\$I\$8:\$I\$446<10),--(ROUND('Rescheduled Master Listing'!\$C\$8:\$C\$446,1)=125.1))

Barry / Pepper -

can you explain what the double dash ( -- ) means in this code please?

Replies
5
Views
200
Replies
3
Views
114
Replies
3
Views
184
Replies
4
Views
342
Replies
9
Views
262

1,203,082
Messages
6,053,417
Members
444,662
Latest member
AaronPMH

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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