SUMPRODUCT Returning N/A#

[coliervile]

Good day everyone-

I'm trying to sum ($AC$7:$BL$7) if ($W$1:$KH$1) is the month is 10 or October.

=SUMPRODUCT((MONTH($W$1:$KH$1)=10)*($AC$7:$BL$7))

The dates in range ($W$1:$KH$1) are formatted as dates.

=SUMPRODUCT((MONTH($W$1:$KH$1)=10)*(YEAR($W$1:$KH$1)=2015)*($AC$7:$BL$7))

I get a N/A# error on both.


Best regards,
Charlie

 

[pgc01]

I get a N/A# error on both.

Hi Charlie

... as you should. The number of columns is not the same in the ranges. W:KH has 272 columns, AC:BL has 36 columns, which results in an error (and makes no sense).

Maybe you can explain your problem with more details. Maybe a simple example with a few columns?

 

[coliervile]

Thanks "pgc01" for looking at my question. You response makes sense and when i adjusted the formula I got the correct result. What I'm trying to do is sum various rows for each month of the Fiscal Year using the calendar dates in the range of W1:KH1. My problem is this... the data below W1:KH1 that I want to sum is offset from column to column in rows 3 to 52 it's not in any order. Which is a real pain.

Charlie

 

[pgc01]

Could post a small example, just some rows and columns, that shows what you need?

May someone can figure out a solution.

 

[coliervile]

"pgc01" unfortunately I'm on a work computer and I can't download Jeanie to post a sampling of my work.