Sumproduct help (I think)

[daniels012]

I have teams in Column B and in Column D
I have dates in Column F
In cell N6 I have a date
In Cell N7 I have a date

I want to put a formula to the right that I can copy down (columns "N" and "O") that will count how many times their name appears between those dates.

The rows would be 59 through 305. So B59:B305

Thank You for any assistance,
Michael

 

[daniels012]

stefankemp,
It is sorry if I messed up in saying different.
Michael

 

[stefankemp]

I tried this in cell N139:

=sumproduct(($B$59:$B$305=B139)+($D$59:$D$305=B139)+($F$59:$F$305>$N$6)+($F$59:$F$305<$N$7))

But it gives 0 as my answer. It should be at least one that I can see for sure.

Michael

Replace the + with *.

 

[daniels012]

Aladin,
I did not! I must have swooped right by it.
Let me check it out

Michael

 

[daniels012]

stefankemp,
I just did, and it comes up with 0?

Michael

 

[daniels012]

Aladin,
What is "Name"?
should I put my cell reference here?

Michael

 

[daniels012]

I think I know why!!!!
I need this:
($B$59:$B$305=B139)+($D$59:$D$305=B139)
to be OR instead of AND
My formula is trying to find the name in both column B and D
I need it to find it in B or D

Michael

 

[stefankemp]

stefankemp,
I just did, and it comes up with 0?

Michael

So in your data is there at least one row where the name in column B is equal to the name in B139, the name in column D is also equal to the name in B139, and the date in column F is after the date in N6 but before the date in N7?

 

[stefankemp]

I think I know why!!!!
I need this:
($B$59:$B$305=B139)+($D$59:$D$305=B139)
to be OR instead of AND
My formula is trying to find the name in both column B and D
I need it to find it in B or D

Michael

Ah, OK yes that would work.

 

[daniels012]

I tried this:

=sumproduct(($B$59:$B$305=B139)*($F$59:$F$305>$N$6)*($F$59:$F$305<$N$7))+sumproduct(($D$59:$D$305=B139)*($F$59:$F$305>$N$6)*($F$59:$F$305<$N$7))

It is still not giving the results I want?

Michael

 

[tony0682]

=SUMPRODUCT(--($F$59:$F$305>=$N$6),--($F$59:$F$305<=$N$7),($B$59:$B$305=N59)+($C$59:$C$305=N59))

Assuming N59 is the name you're trying to look up and the formula goes in O