Sumproduct and Month Function

  • Thread starter Thread starter Mike Lee
  • Start date Start date
M

Mike Lee

Hello,
I have a sheet which lists transactions in rows. I'm
trying to put together a grid which will show totals for
various transaction types for each month. For the
purposes of this formula, I have 3 named ranges;
trans.code, trans.date and trans.amt. An example of the
formula I tried:

=Sumproduct((trans.code = B14)*(Month(trans.date) = C9),
(trans.amt))

where row 9 contains numbers 1 through 12 corresponding
with the month, and column B holds the different
transaction codes.

However, I get a #Value! error, which I assume has
something to do with trying to use the Month function
within the Sumproduct function. All 3 ranges have the
same number of elements.

Any ideas on whether I would need to use an array formula
instead, or if there's a way to get the Sumproduct to
work?

Thanks to all for the help.

Mike
 
If the date range have text values that would be the result.
Select the date range, press F5, select special and constants, uncheck
everything but text
and see if anything gets selected
 
Hi

To define a month, you must have a year too. For case C9 contains a date
formatted as "mmm" or "mmm.yyyy" or ...
=Sumproduct((trans.code = B14)*(trans.date >
DATE(YEAR(C9),MONTH(C9),0)*(trans.date < DATE(YEAR(C9),MONTH(C9)+1,1)*())

When all months are from current year, and C9 contains month number
=Sumproduct((trans.code = B14)*(trans.date >
DATE(YEAR(TODAY()),C9,0)*(trans.date < DATE(YEAR(TODAY()),C9+1,1)*())
 
Ahhhhh....thank you very much.

The header is part of the range. That would explain why
the Month function isn't working.

Thanks again.

Mike
 
Hi


Peo Sjoblom said:
Hi Arvi,

It isn't necessary.

Month(range)=2
Click to expand...


Only when all dates are from same year, or when the year isn't significant


Arvi Laanemets
 
Back
Top