Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumProduct but sometimes don't test some criteria | Excel Worksheet Functions | |||
Test Cell For Multiple Criteria | Excel Worksheet Functions | |||
Calculate mean of test scores from rows of test answers | Excel Discussion (Misc queries) | |||
Using Error Message as test Criteria | Excel Worksheet Functions | |||
results of "SUMIF" test criteria | Excel Worksheet Functions |