Need to lookup a value in a table and sum only the values relating to the month

[deadparrot]

Hi,

I have been scratching my head over this. I want to basically summarize daily data into monthly which would normally be a sumif, but I also want it to look up the Row first. In the example in bold (and attached) below I want a formula that looks up the row that the Apple is in and then add up all the values in Oct.

Oct Oct Oct Nov Nov Nov Dec Dec Dec
Apple 1 1 1 1 1 1 1 1 1
Banana 2 2 2 2 2 2 2 2 2
Carrot 3 3 3 3 3 3 3 3 3

Oct Nov
Apple 3 3
Banana 6 6

Any help or even just the formula to use would be great

[JohnTopley]

Set B7 to first of month e.g 01/10/2021 and format as "mmm" C7 01/11/2021

in B8

=SUMPRODUCT(($B$3:$J$5)*($B$2:$J$2>= B$7)*($B$2:$J$2<=EOMONTH(B$7,0))*($A$3:$A$5=$A8))

copy down and across

[Phuocam]

Try this:

=SUMIF($B$1:$J$1,B$7,INDEX($B$3:$J$5,MATCH($A8,$A$3:$A$5,0),0))

[deadparrot]

That is amazing. It has been keeping me up at night! Thanks both for such a fast response.