help update my sumproduct please

[BrutalLogiC]

=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!

 

[Michael M]

your ranges must be the same length...

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

 

[BrutalLogiC]

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

 

[Michael M]

It seems you are trying to compare a row of years against a column of names to get a return from a Block of cells ???
I'd suggest either posting a smaple of the data, see here for methods on doing that

https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


or uploading your worksheet to dropbox and posting a link back here

 

[BrutalLogiC]

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]

you could also try using dropbox !

I'm guessing though, that you need to look at an INDEX / MATCH formula
A few samples of that formula can be found here

https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/

 

[Rastaman]

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]

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]

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]

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