EOMONTH as test criteria
 

Is it possible to use EOMONTH as a test criteria in a sumproduct array?

I have the EOMONTH set up as column headers on my worksheet to show a 7
month window of data (3 months prior, current month, and 3 months future),
this will always be current based on the current date. I want to pull data
from a separate work sheet based on monthly totals, but I would like to
incorporate the EOMONTH formula into the sumproduct so the data stays current
with the column headers.

EOMONTH as test criteria
 

Is it possible to use EOMONTH as a test
criteria in a sumproduct array?

Yes, but EOMONTH won't work on arrays.

This *won't* work:

=SUMPRODUCT(--(A1:A10=EOMONTH(A1:A10,0)))

This will work:

=SUMPRODUCT(--(A1:A10=EOMONTH(B1,0)))

--
Biff
Microsoft Excel MVP


"ocuhcs" wrote in message
...
Is it possible to use EOMONTH as a test criteria in a sumproduct array?

I have the EOMONTH set up as column headers on my worksheet to show a 7
month window of data (3 months prior, current month, and 3 months future),
this will always be current based on the current date. I want to pull data
from a separate work sheet based on monthly totals, but I would like to
incorporate the EOMONTH formula into the sumproduct so the data stays
current
with the column headers.

EOMONTH as test criteria
 

Biff,
Thanks for the help.
I guess there was more wrong with my sumproduct than just the EOMONTH
(although I did modify it as you suggested).
Here is my formula:
=SUMPRODUCT(--('Wholesale Orders'!M2:M9000=EOMONTH(A1,0)),--('Wholesale
Orders'!E2:E9000='Project Forecast'!A3),--('Wholesale Orders'!S2:S9000))

Array1 is the date column, array 2 is the project label, and array 3 is the
value I am trying to sum.

It should be pulling a positive value, but it is returning a zero value.

"T. Valko" wrote:

Is it possible to use EOMONTH as a test
criteria in a sumproduct array?

Yes, but EOMONTH won't work on arrays.

This *won't* work:

=SUMPRODUCT(--(A1:A10=EOMONTH(A1:A10,0)))

This will work:

=SUMPRODUCT(--(A1:A10=EOMONTH(B1,0)))

--
Biff
Microsoft Excel MVP


"ocuhcs" wrote in message
...
Is it possible to use EOMONTH as a test criteria in a sumproduct array?

I have the EOMONTH set up as column headers on my worksheet to show a 7
month window of data (3 months prior, current month, and 3 months future),
this will always be current based on the current date. I want to pull data
from a separate work sheet based on monthly totals, but I would like to
incorporate the EOMONTH formula into the sumproduct so the data stays
current
with the column headers.

EOMONTH as test criteria
 

Ok, that means you have a data problem.

The dates in M2:M9000 might not be true Excel dates.

There may be leading/trailing whitespace characters in E2:E9000 or 'Project
Forecast'!A3.

Find one single row where M = EOMONTH(A1,0) and E = 'Project Forecast'!A3
then try the formula on just that single row: If you get a result of 0 then
that will confirm that you have a problem with your data.

--
Biff
Microsoft Excel MVP


"ocuhcs" wrote in message
...
Biff,
Thanks for the help.
I guess there was more wrong with my sumproduct than just the EOMONTH
(although I did modify it as you suggested).
Here is my formula:
=SUMPRODUCT(--('Wholesale Orders'!M2:M9000=EOMONTH(A1,0)),--('Wholesale
Orders'!E2:E9000='Project Forecast'!A3),--('Wholesale Orders'!S2:S9000))

Array1 is the date column, array 2 is the project label, and array 3 is
the
value I am trying to sum.

It should be pulling a positive value, but it is returning a zero value.

"T. Valko" wrote:

Is it possible to use EOMONTH as a test
criteria in a sumproduct array?

Yes, but EOMONTH won't work on arrays.

This *won't* work:

=SUMPRODUCT(--(A1:A10=EOMONTH(A1:A10,0)))

This will work:

=SUMPRODUCT(--(A1:A10=EOMONTH(B1,0)))

--
Biff
Microsoft Excel MVP


"ocuhcs" wrote in message
...
Is it possible to use EOMONTH as a test criteria in a sumproduct array?

I have the EOMONTH set up as column headers on my worksheet to show a 7
month window of data (3 months prior, current month, and 3 months
future),
this will always be current based on the current date. I want to pull
data
from a separate work sheet based on monthly totals, but I would like to
incorporate the EOMONTH formula into the sumproduct so the data stays
current
with the column headers.