software

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

Assumptions:

B1:G1 contains the column labels

A2:A6 contains the date

B2:G6 contains the data

Formula:

=SUMPRODUCT((A2:A6gt;=quot;2006/1/2quot; 0)*(A2:A6lt;=quot;2006/1/4quot; 0)*(B1:G1=quot;Squot;)*(B2:G
6))

Hope this helps!

In article gt;,
quot;angelilaquot; gt; wrote:

gt; I have a spreadsheet that looks like the following:
gt;
gt; S L M S S L
gt; 1/1/06 2 3 5 4 3 2
gt; 1/2/06 1 5 3 2 7 1
gt; 1/3/06 0 4 2 3 8 1
gt; 1/4/06 4 7 5 7 9 1
gt; 1/5/06 7 1 1 7 0 8
gt;
gt; I would like to be able to sum values for all 'S' columns between any two
gt; dates, like between 1/2/06 and 1/4/06, without including all dates in my
gt; equation.
gt;
gt; Any help would be greatly appreciated.
gt;
gt; -Thanks

Hi Angelila
Try:
=SUMPRODUCT((A2:A100gt;=H1)*(A2:A100lt;=H2)*(B1:G1=quot;Squot; ),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

quot;angelilaquot; wrote:

gt; I have a spreadsheet that looks like the following:
gt;
gt; S L M S S L
gt; 1/1/06 2 3 5 4 3 2
gt; 1/2/06 1 5 3 2 7 1
gt; 1/3/06 0 4 2 3 8 1
gt; 1/4/06 4 7 5 7 9 1
gt; 1/5/06 7 1 1 7 0 8
gt;
gt; I would like to be able to sum values for all 'S' columns between any two
gt; dates, like between 1/2/06 and 1/4/06, without including all dates in my
gt; equation.
gt;
gt; Any help would be greatly appreciated.
gt;
gt; -Thanks

Thanks!

Hi Domenic and Pinmaster

I had set up the data exactly the same as you and tried the formula
=SUMPRODUCT(--(A2:A6gt;=J1),--(A2:A6lt;=K1),--(B1:G1=quot;Squot;),(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:A6gt;=J1)*(A2:A6lt;=K1)*(B1:G1=quot;Squot;)*(B 2: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.
--
Regards

Roger Govierquot;Domenicquot; gt; wrote in message
...
gt; Assumptions:
gt;
gt; B1:G1 contains the column labels
gt;
gt; A2:A6 contains the date
gt;
gt; B2:G6 contains the data
gt;
gt; Formula:
gt;
gt; =SUMPRODUCT((A2:A6gt;=quot;2006/1/2quot; 0)*(A2:A6lt;=quot;2006/1/4quot; 0)*(B1:G1=quot;Squot;)*(B2:G
gt; 6))
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; quot;angelilaquot; gt; wrote:
gt;
gt;gt; I have a spreadsheet that looks like the following:
gt;gt;
gt;gt; S L M S S L
gt;gt; 1/1/06 2 3 5 4 3 2
gt;gt; 1/2/06 1 5 3 2 7 1
gt;gt; 1/3/06 0 4 2 3 8 1
gt;gt; 1/4/06 4 7 5 7 9 1
gt;gt; 1/5/06 7 1 1 7 0 8
gt;gt;
gt;gt; I would like to be able to sum values for all 'S' columns between any
gt;gt; two
gt;gt; dates, like between 1/2/06 and 1/4/06, without including all dates in
gt;gt; my
gt;gt; equation.
gt;gt;
gt;gt; Any help would be greatly appreciated.
gt;gt;
gt;gt; -Thanks
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:E5gt;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:E5gt;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!

In article gt;,
quot;Roger Govierquot; gt; wrote:

gt; Hi Domenic and Pinmaster
gt;
gt; I had set up the data exactly the same as you and tried the formula
gt; =SUMPRODUCT(--(A2:A6gt;=J1),--(A2:A6lt;=K1),--(B1:G1=quot;Squot;),(B2:G6))
gt; where J1 was holding my starting date 02/01/06 (UK dates) and K1 the
gt; ending date 04/01/06
gt; My result was #VALUE
gt;
gt; I put it down to either one array being horizontal and the others
gt; vertical, or arrays of different sizes and I had thought that SUMPRODUCT
gt; always required identical array sizes. I couldn't be bothered to spend
gt; further time on it right then, and continued with my work.
gt;
gt; On coming back to the forum, I noticed the solutions you had each
gt; posted, so I went back to the test sheet and changed it from using the
gt; double unary minus to coerce The true's and False's, to using the *
gt; operator as you had each done.
gt; =SUMPRODUCT((A2:A6gt;=J1)*(A2:A6lt;=K1)*(B1:G1=quot;Squot;)*(B 2:G6))
gt; This returns the correct answer of 41 (as does each of your formulae).
gt;
gt; I am at a loss to understand this.
gt; Do you know why the -- doesn't work in this scenario and the * operator
gt; does?
gt;
gt; I can now see that there is no problem with the array size, as we have 5
gt; vertical tests, and 6 horizontal tests resulting in 1's and 0's being
gt; used to multiply a matrix of 30 numerical values. But why does the
gt; coercion not work in this case, where I have never had if fail in other
gt; cases?
gt; Any light that you (or others) can throw on this would be most welcome.
gt;
gt;
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; quot;Domenicquot; gt; wrote in message
gt; ...
gt; gt; Assumptions:
gt; gt;
gt; gt; B1:G1 contains the column labels
gt; gt;
gt; gt; A2:A6 contains the date
gt; gt;
gt; gt; B2:G6 contains the data
gt; gt;
gt; gt; Formula:
gt; gt;
gt; gt; =SUMPRODUCT((A2:A6gt;=quot;2006/1/2quot; 0)*(A2:A6lt;=quot;2006/1/4quot; 0)*(B1:G1=quot;Squot;)*(B2:G
gt; gt; 6))
gt; gt;
gt; gt; Hope this helps!
gt; gt;
gt; gt; In article gt;,
gt; gt; quot;angelilaquot; gt; wrote:
gt; gt;
gt; gt;gt; I have a spreadsheet that looks like the following:
gt; gt;gt;
gt; gt;gt; S L M S S L
gt; gt;gt; 1/1/06 2 3 5 4 3 2
gt; gt;gt; 1/2/06 1 5 3 2 7 1
gt; gt;gt; 1/3/06 0 4 2 3 8 1
gt; gt;gt; 1/4/06 4 7 5 7 9 1
gt; gt;gt; 1/5/06 7 1 1 7 0 8
gt; gt;gt;
gt; gt;gt; I would like to be able to sum values for all 'S' columns between any
gt; gt;gt; two
gt; gt;gt; dates, like between 1/2/06 and 1/4/06, without including all dates in
gt; gt;gt; my
gt; gt;gt; equation.
gt; gt;gt;
gt; gt;gt; Any help would be greatly appreciated.
gt; gt;gt;
gt; gt;gt; -Thanks