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

 

[stefankemp]

Try

=SUMPRODUCT(($B$59:$B$305=$B59)*($F$59:$F$305>=$N$6)*($F$59:$F$305<=$N$7))

in cell N59, and copy down. Similar for column O.

 

[number kruncher]

Michael,

You could use the COUNTIFS formula e.g.

Also, you need to have seperate criteria cells. In O6 say enter =">="&N6
and in O7 enter ="<="&N7

Then use

=COUNTIFS(B$59:B$305,O$6,B$59:B$305,O$7)

 

[T. Valko]

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

Can you post a small amount of sample data that shows your setup and lets us know what result you expect.

 

[daniels012]

stephankemp,
I am going to try this.
How does this account for the team name in B59 being in Column D as well?
Because I want to count the number of times the team name (B59) shows up in between the dates given in columns B and D


Michael D

 

[Aladin Akyurek]

You did not specify which names?

Maybe something like...

Control+shift+enter, not just enter:

=SUM(IF($F$59:$F$305>=N6,IF($F$59:$F$305<=N7,($B$5:$B$305=Name)+($D$5:$D$305))))

Is this even close?

 

[daniels012]

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

 

[stefankemp]

Daniel, I thought your date range was in cells N6 and N7? With dates to compare in column F?

 

[Aladin Akyurek]

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

Michael

Did you see my suggestion?

 

[number kruncher]

Do you need to include the dates in N6 and N7 in your search?

>= and <= need to to used if so.