sum products mult arrays

[dmheller]

All,
I have a sum products that is working for individual things.
=SUMPRODUCT(--(MONTH($B$15:$B$1010)=MONTH($AF$3)),--(YEAR($B$15:$B$1010)=YEAR($AF$2)),X15:X1010)
I then write one with mult arrays
=(SUMPRODUCT(--(MONTH($B$15:$B$1010)=MONTH(AF3)),--(YEAR($B$15:$B$1010)=YEAR(AF2)),$D$15:D1010,$F$15:F1010,$H$15:H1010,$J$15:J1010,$L$15:L1010,$N$15:N1010,$P$15:P1010,$R$15:R1010,$T$15:T1010,$V$15:V1010,$X$15:X1010))
The multi array returns 0 and I know there is data in X as it returns 1.4 as an individual array. Any help would be great
B is just dates,
The arrays are just numbers

 

[Jonmo1]

0's or blanks in any of the multiple ranges will create a 0 result for that row.

Perhaps you want to ADD each of the multiple ranges.
Try
=(SUMPRODUCT(--(MONTH($B$15:$B$1010)=MONTH(AF3)),--(YEAR($B$15:$B$1010)=YEAR(AF2)),$D$15:D1010+$F$15:F1010+$H$15:H1010+$J$15:J1010+$L$15:L1010+$N$15:N1010+$P$15:P1010+$R$15:R1010+$T$15:T1010+$V$15:V1010+$X$15:X1010))

 

[dmheller]

I do that and it returns #value

 

[dmheller]

There are lots of blanks but there are no errors in the data.

 

[Jonmo1]

Are there text values in there?
If those additional columns contain formulas, and some of them are returning blank (""), that will cause this problem. Number+Text=#Value!.
I would suggest putting the sum in a helper column, say Z15
Z15 and filled down
=SUM(D15,F15,H15,J15,L15,N15,P15,R15,T15,V15,X15)

Then use
=SUMPRODUCT(--(MONTH($B$15:$B$1010)=MONTH($AF$3)),--(YEAR($B$15:$B$1010)=YEAR($AF$2)),Z15:Z1010)

 

[dmheller]

Ok, thank you.