# help update my sumproduct please

#### BrutalLogiC

##### Board Regular
=SUMPRODUCT((YEAR(\$D\$24:\$AN\$24)=H2)*(\$D\$25:\$AN\$36))

trying to adjust above so the full formula is returning the value based on:

1. the year in D24:AN24 being equal to H2; and
2. the criteria in B25:B35 being equal to E3

with the values in the range D25:AN36

help!

Last edited:

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### Michael M

##### Well-known Member
your ranges must be the same length...

Code:
``=SUMPRODUCT((YEAR(\$D\$24:\$AN\$[color=red]36[/color])=H2)*(\$D\$25:\$AN\$36))``

• BrutalLogiC

#### BrutalLogiC

##### Board Regular
I didn't explain well enough as the original formula works for me but only considers the years (point 1), I am probably trying to edit a formula when I need a completely different one?

D24:AN24 is a list of dates which are between the years 2019 and 2022
B25:B35 is a list of names (John, Mo, Seb etc.)
I'm trying to write formula which returns the sum of the range D25:AN36 where the year is X and the name is Y

#### BrutalLogiC

##### Board Regular

I'm trying to compare the row against a year and the column against a name and when the two match in the block of cells I want the result.. now I'm trying to work out how to use the MrExcel HTML maker #### Michael M

##### Well-known Member
Last edited:
• BrutalLogiC

#### Rastaman

##### Board Regular

Not sure if I understand your request as your stated ranges are a little confusing. Is this what you are looking for (adjust for your ranges)?

=SUMPRODUCT((YEAR(\$D\$24:\$J\$24)=\$H\$2)*(\$D\$25:\$J\$36=(\$B\$25:\$B\$36))*(\$D\$25:\$J\$36))

Rick

• BrutalLogiC

##### MrExcel MVP
I didn't explain well enough as the original formula works for me but only considers the years (point 1), I am probably trying to edit a formula when I need a completely different one?

D24:AN24 is a list of dates which are between the years 2019 and 2022
B25:B35 is a list of names (John, Mo, Seb etc.)
I'm trying to write formula which returns the sum of the range D25:AN36 where the year is X and the name is Y

Control+shift+enter, not just enter:

=SUM(IF(D24:AN24=2020,IF(B25:B36="Mo",D25:AN36)))

• BrutalLogiC

#### BrutalLogiC

##### Board Regular
Not sure if I understand your request as your stated ranges are a little confusing. Is this what you are looking for (adjust for your ranges)?

=SUMPRODUCT((YEAR(\$D\$24:\$J\$24)=\$H\$2)*(\$D\$25:\$J\$36=(\$B\$25:\$B\$36))*(\$D\$25:\$J\$36))

Rick

Thanks this works for me thanks:
=SUMPRODUCT((YEAR(\$D\$25:\$AN\$25)=H\$3)*(\$B\$26:\$B\$36=(\$E4))*(\$D\$26:\$AN\$36))

Control+shift+enter, not just enter:

=SUM(IF(D24:AN24=2020,IF(B25:B36="Mo",D25:AN36)))

{=SUM(IF(\$D\$25:\$AN\$25=H\$3,IF(\$B\$26:\$B\$36=\$E4,\$D\$26:\$AN\$36)))} thanks, I'm trying this but no result

I think it's because D25:AN25 is in format mmm-yy and H3 is just a number 2019 so can't read it properly?

##### MrExcel MVP
Thanks this works for me thanks:
=SUMPRODUCT((YEAR(\$D\$25:\$AN\$25)=H\$3)*(\$B\$26:\$B\$36=(\$E4))*(\$D\$26:\$AN\$36))

{=SUM(IF(\$D\$25:\$AN\$25=H\$3,IF(\$B\$26:\$B\$36=\$E4,\$D\$26:\$AN\$36)))} thanks, I'm trying this but no result

I think it's because D25:AN25 is in format mmm-yy and H3 is just a number 2019 so can't read it properly?

Control+shift+enter, not just enter:

=SUM(IF(YEAR(\$D\$25:\$AN\$25)=H\$3,IF(\$B\$26:\$B\$36=\$E4,\$D\$26:\$AN\$36)))

• BrutalLogiC

Replies
4
Views
97
Replies
2
Views
80
Replies
5
Views
155
Replies
0
Views
88
Replies
10
Views
157