Help extracting year & text for sumif or sum product

[ehous31900]

I am trying to calculate sums off columns based on the year extracted from a Cell (month, day, year) and a cell with text ROUVY.

The application is tracking milage over many years extracting totals per year and extracting rouvy (indoor riding) from outdoor riding.

I have the formula working separately, However, I have not been able to combined them.
The following works:
=SUMPRODUCT((YEAR(B9:B24)=B1)*D9:D24)
=SUMPRODUCT(--(YEAR(B9:B24)=B1),D9:D24)
=SUMIF($A$9:$A10000,"ROU*",$D$9:D10000)

this does not work:
=SUMPRODUCT(IF(YEAR(B9:B24)=B1,1,0),IF(A9:A24="ROU*",1,0),D9:D24)

Thanks, Ed

 

[Joe4]

Welcome to the Board!

Try this:

=SUMPRODUCT(--(YEAR(B9:B24)=B1),--(LEFT(A9:A24,3)="ROU"),D9:D24)

 

[ehous31900]

It worked thank you, would have never thought about LEFT.

 

[FormR]

Hi, as an alternative, you could also try a SUMIFS() like this.

=SUMIFS(D9:D24,B9:B24,">="&DATE(B1,1,1),B9:B24,"<="&DATE(B1,12,31),A9:A24,"ROU*")

 

[Joe4]

You are welcome.
Glad we were able to help!