COUNTIF (2) Conditions

[Featherk]

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

 

[NBVC]

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"

 

[barry houdini]

Welcome to the board

Try

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

 

[Featherk]

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.

 

[NBVC]

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.

 

[Featherk]

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.

 

[Scott Huish]

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

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

 

[Featherk]

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.

The weekend is almost here and I have a bad headache about this....

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

 

[barry houdini]

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))

 

[Analyze This]

Barry / Pepper -

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