J
jrproudfoot
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))))
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))))