Excel Summing Formula with Date of Month using SUMPRODUCT
I am trying to sum rows having the same date of the month. I have a formula that works on columns but fails when rows are requested. I will show the formulas that I tried, but now I am stuck. Any advice would be much appreciated.
=SUMPRODUCT(--(MONTH($A$9:$A$50)=5),$B$9:$B$50)
This array works fine with column
=SUMPRODUCT(--(MONTH($A$9:$A$50)=5),$B$9:**$H$50**)
see the bold link which contains a series of cells arranged in rows.
source to share
SUMPRODUCT
typically expects ranges to be the same size, but you can get the result you want with a syntax change, i.e.
=SUMPRODUCT((MONTH($A$9:$A$50)=5)*$B$9:$H$50)
You cannot have text in B9:H50
, otherwise you will get an error #VALUE!
. If you have to render text in this range you can use this version which should ignore the text and give you the result
=SUMPRODUCT((MONTH($A$9:$A$50)=5)*ISNUMBER($B$9:$H$50),$B$9:$H$50)
source to share