Complicated SUMPRODUCT OR SUMIF question?

[Guest]

I have a spreadsheet that looks like the following:

S L M S S L
1/1/06 2 3 5 4 3 2
1/2/06 1 5 3 2 7 1
1/3/06 0 4 2 3 8 1
1/4/06 4 7 5 7 9 1
1/5/06 7 1 1 7 0 8

I would like to be able to sum values for all 'S' columns between any two
dates, like between 1/2/06 and 1/4/06, without including all dates in my
equation.

Any help would be greatly appreciated.

-Thanks

 

[Domenic]

Assumptions:

B1:G1 contains the column labels

A2:A6 contains the date

B2:G6 contains the data

Formula:

=SUMPRODUCT((A2:A6>="2006/1/2"+0)*(A2:A6<="2006/1/4"+0)*(B1:G1="S")*(B2:G
6))

Hope this helps!

 

[Guest]

Hi Angelila
Try:
=SUMPRODUCT((A2:A100>=H1)*(A2:A100<=H2)*(B1:G1="S"),B2:G100))
where A2:A100 are dates, H1 start date, H2 end date, B1:G1 column headers
with the letters and B2:G100 range with numbers.

HTH
JG

 

[Guest]

Thanks!

 

[Roger Govier]

Hi Domenic and Pinmaster

I had set up the data exactly the same as you and tried the formula
=SUMPRODUCT(--(A2:A6>=J1),--(A2:A6<=K1),--(B1:G1="S"),(B2:G6))
where J1 was holding my starting date 02/01/06 (UK dates) and K1 the
ending date 04/01/06
My result was #VALUE

I put it down to either one array being horizontal and the others
vertical, or arrays of different sizes and I had thought that SUMPRODUCT
always required identical array sizes. I couldn't be bothered to spend
further time on it right then, and continued with my work.

On coming back to the forum, I noticed the solutions you had each
posted, so I went back to the test sheet and changed it from using the
double unary minus to coerce The true's and False's, to using the *
operator as you had each done.
=SUMPRODUCT((A2:A6>=J1)*(A2:A6<=K1)*(B1:G1="S")*(B2:G6))
This returns the correct answer of 41 (as does each of your formulae).

I am at a loss to understand this.
Do you know why the -- doesn't work in this scenario and the * operator
does?

I can now see that there is no problem with the array size, as we have 5
vertical tests, and 6 horizontal tests resulting in 1's and 0's being
used to multiply a matrix of 30 numerical values. But why does the
coercion not work in this case, where I have never had if fail in other
cases?
Any light that you (or others) can throw on this would be most welcome.

 

[Domenic]

Actually, it has nothing to do with the double negative. Unlike the
star syntax, the array arguments for the comma syntax have to be the
same size. So, for example, you can have the following formula...

=SUMPRODUCT((A2:A5=G2)*(B1:E1=H2),--(B2:E5>K2))

Notice that the first argument...

(A2:A5=G2)*(B1:E1=H2)

....creates a 4 row by 4 column array, and the second argument...

--(B2:E5>K2)

....also creates a 4 row by 4 column array. Both arguments are the same
size and, hence, can be multiplied together.

Hope this helps!