Sumproduct, By Quarters and Amount

[Guest]

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)

 

[Guest]

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)

 

[Myrna Larson]

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.

 

[Guest]

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

 

[Guest]

Glad it helped, Tom !
Thanks for the feedback ..

 

[Harlan Grove]

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:F500<23))