help update my sumproduct please

BrutalLogiC

Board Regular
Joined
Feb 26, 2006
Messages
247
Office Version
  1. 2016
Platform
  1. Windows
=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:

Some videos you may like

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
Joined
Oct 27, 2005
Messages
19,267
Office Version
  1. 2013
Platform
  1. Windows
your ranges must be the same length...

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

BrutalLogiC

Board Regular
Joined
Feb 26, 2006
Messages
247
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Feb 26, 2006
Messages
247
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Rastaman

Board Regular
Joined
Feb 18, 2006
Messages
236

ADVERTISEMENT

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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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

Board Regular
Joined
Feb 26, 2006
Messages
247
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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)))
 

Watch MrExcel Video

Forum statistics

Threads
1,109,205
Messages
5,527,399
Members
409,760
Latest member
zeeshansyed

This Week's Hot Topics

Top