Sumproduct, By Quarters and Amount
 

I have been trying to use SUMPRODUCT to count the number of data entries that
are less than 23 (column F) by quarter (date is in column C). I can't seem
to get it to work. I would also like to then calculate the average in a
similar fashion. i can't get it to count though. I have also tried to use
datevalue with the dates.

=SUMPRODUCT(--(Log!C2:C500<"4/1/04")*(Log!F2:F500<23)


--
Sincerely,
Tom Fortune

Perhaps something along these lines ..

Assuming col C may contain a mixture of dates straddling a couple of years

To compute for the *1st* Quarter of 2004:

For count,

Try (press ENTER):
=SUMPRODUCT((YEAR(Log!C2:C500)=2004)*(MONTH(Log!C2 :C500)=1)*(MONTH(Log!C2:C500)<=3)*(Log!F2:F500<23 ))

For average,

Try (Array-entered, i.e. press CTRL+SHIFT+ENTER)
=AVERAGE(IF((YEAR(Log!C2:C500)=2004)*(MONTH(Log!C2 :C500)=1)*(MONTH(Log!C2:C500)<=3)*(Log!F2:F500<23 ),Log!F2:F500))

Adapt the formulas accordingly for the quarter* and/or year:
*Example, for:
2nd quarter: .. (MONTH(Log!C2:C500)=4)*(MONTH(Log!C2:C500)<=6)
3rd quarter: .. (MONTH(Log!C2:C500)=7)*(MONTH(Log!C2:C500)<=9)
4th quarter: .. (MONTH(Log!C2:C500)=10)*(MONTH(Log!C2:C500)<=12)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"Tom Fortune" wrote:

I have been trying to use SUMPRODUCT to count the number of data entries that
are less than 23 (column F) by quarter (date is in column C). I can't seem
to get it to work. I would also like to then calculate the average in a
similar fashion. i can't get it to count though. I have also tried to use
datevalue with the dates.

=SUMPRODUCT(--(Log!C2:C500<"4/1/04")*(Log!F2:F500<23)


--
Sincerely,
Tom Fortune

Try it as

=SUMPRODUCT((Log!C2:C500<DATEVALUE("4/1/04"))*(Log!F2:F500<23))

SUMPRODUCT doesn't automatically convert text that looks like a date to you
and me into a date (which is a number).

If you are multiplying the two arrays, you don't need the --. The * takes care
of coercion of TRUE/FALSE to 1/0.


On Sun, 23 Jan 2005 19:27:01 -0800, "Tom Fortune"
wrote:

I have been trying to use SUMPRODUCT to count the number of data entries that
are less than 23 (column F) by quarter (date is in column C). I can't seem
to get it to work. I would also like to then calculate the average in a
similar fashion. i can't get it to count though. I have also tried to use
datevalue with the dates.

=SUMPRODUCT(--(Log!C2:C500<"4/1/04")*(Log!F2:F500<23)

WOW, that was fast! They work. I can use these ideas to so others with as
well. Thanks!

"Max" wrote:

Perhaps something along these lines ..

Assuming col C may contain a mixture of dates straddling a couple of years

To compute for the *1st* Quarter of 2004:

For count,

Try (press ENTER):
=SUMPRODUCT((YEAR(Log!C2:C500)=2004)*(MONTH(Log!C2 :C500)=1)*(MONTH(Log!C2:C500)<=3)*(Log!F2:F500<23 ))

For average,

Try (Array-entered, i.e. press CTRL+SHIFT+ENTER)
=AVERAGE(IF((YEAR(Log!C2:C500)=2004)*(MONTH(Log!C2 :C500)=1)*(MONTH(Log!C2:C500)<=3)*(Log!F2:F500<23 ),Log!F2:F500))

Adapt the formulas accordingly for the quarter* and/or year:
*Example, for:
2nd quarter: .. (MONTH(Log!C2:C500)=4)*(MONTH(Log!C2:C500)<=6)
3rd quarter: .. (MONTH(Log!C2:C500)=7)*(MONTH(Log!C2:C500)<=9)
4th quarter: .. (MONTH(Log!C2:C500)=10)*(MONTH(Log!C2:C500)<=12)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"Tom Fortune" wrote:

I have been trying to use SUMPRODUCT to count the number of data entries that
are less than 23 (column F) by quarter (date is in column C). I can't seem
to get it to work. I would also like to then calculate the average in a
similar fashion. i can't get it to count though. I have also tried to use
datevalue with the dates.

=SUMPRODUCT(--(Log!C2:C500<"4/1/04")*(Log!F2:F500<23)


--
Sincerely,
Tom Fortune

Glad it helped, Tom !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"Tom Fortune" wrote:

WOW, that was fast! They work. I can use these ideas to so others with as
well. Thanks!

"Myrna Larson" wrote...
Try it as

=SUMPRODUCT((Log!C2:C500<DATEVALUE("4/1/04"))*(Log!F2:F500<23))

SUMPRODUCT doesn't automatically convert text that looks like a date to you
and me into a date (which is a number).
....

It's not SUMPRODUCT per se. Rather, Excel itself always returns TRUE when
checking if any number is less than any text string. So the first expression
within SUMPRODUCT is entirely determined by Excel's own semantics.

But it's easy enough to force number-to-number comparison.

=SUMPRODUCT((Log!C2:C500<--"4/1/04")*(Log!F2:F500<23))

However, if using static date strings, if internationalization is ever an
issue, FAR BETTER to use DATE, as in

=SUMPRODUCT((Log!C2:C500<DATE(2004,4,1))*(Log!F2:F 500<23))