Prev: Conditional Formatting
Next: count similar text with using filters
From: jrproudfoot on 5 May 2010 08:14
I have the following Excel calculation, shown below. It is searching cells
A6:A1000 on various sheets for data which fall between a certain date/period
range, and is then adding the values in cells L6:L1000 together to display on
the summary.

The problem I have is that the data in ranges L6:L1000 are supposed to be
Dates, but the only way i can return a value without an error is to use a
number (e.g. of 1) instead. How can I use Dates within cells L6:L1000, and
still return the same totals?

Many thanks in advance for any tips you can offer!

=SUMPRODUCT((Office!L6:L1000)*(Office!A6:A1000>=40269)*(Office!A6:A1000<=40299)+((Commercial!L6:L1000)*(Commercial!A6:A1000>=40269)*(Commercial!A6:A1000<=40299)+((Hospitality!L6:L1000)*(Hospitality!A6:A1000>=40269)*(Hospitality!A6:A1000<=40299))))

From: Bob Phillips on 5 May 2010 08:35
Do you mean like this

=SUMPRODUCT((ISNUMBER(Office!L6:L1000))*(Office!A6:A1000>=--"2010-04-01")*(Office!A6:A1000<--"2010-05-01")
+((ISNUMBER(Commercial!L6:L1000))*(Commercial!A6:A1000>=--"2010-04-01")*(Commercial!A6:A1000<--"2010-05-01")
+((ISNUMBER(Hospitality!L6:L1000))*(Hospitality!A6:A1000>=--"2010-04-01")*(Hospitality!A6:A1000<--"2010-05-01"))))

--

HTH

Bob

"jrproudfoot" <jrproudfoot(a)discussions.microsoft.com> wrote in message
news:D4804884-7203-4226-AACB-E565FCF6F985(a)microsoft.com...
>I have the following Excel calculation, shown below. It is searching cells
> A6:A1000 on various sheets for data which fall between a certain
> date/period
> range, and is then adding the values in cells L6:L1000 together to display
> on
> the summary.
>
> The problem I have is that the data in ranges L6:L1000 are supposed to be
> Dates, but the only way i can return a value without an error is to use a
> number (e.g. of 1) instead. How can I use Dates within cells L6:L1000, and
> still return the same totals?
>
> Many thanks in advance for any tips you can offer!
>
> =SUMPRODUCT((Office!L6:L1000)*(Office!A6:A1000>=40269)*(Office!A6:A1000<=40299)+((Commercial!L6:L1000)*(Commercial!A6:A1000>=40269)*(Commercial!A6:A1000<=40299)+((Hospitality!L6:L1000)*(Hospitality!A6:A1000>=40269)*(Hospitality!A6:A1000<=40299))))
>


 | 
Pages: 1
Prev: Conditional Formatting
Next: count similar text with using filters