Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple sumproduct with dates
Hi -
I'm having trouble with a sumproduct formula on tab1. =SUMPRODUCT(--(tab2!J4:J200=O8),--(ISNUMBER(SEARCH(E5,tab2!C4:C200))),--(tab2!AA4:AA200=A23),tab2!O4:O200) E5 is a 3-letter month representation (i.e. AUG), which is text, based on another cell on the same tab (tab1) which is a date and formatted as such. Column C on 'tab2' is dates, but, the problem I think is that all the columns on 'tab2' are themselves products of sumproduct formulas pointing to yet another tab (tab3). But, I can't go directly from tab1 to tab3 (tab 3 is a raw data tab), because the formulas would get much more complicated. I use a similar technique on a different document where I can go directly from the 1st tab to the 'raw data' tab, and it works: =SUMPRODUCT(--(tab3!I4:I200=O8),--(MONTH(tab3!B4:B200)=(MONTH(A2))),--(tab3!R4:R200=A23),tab3!O4:O200) In case you're wondering, I had tried the MONTH function first, but that gave a #VALUE error when used on the new document that has a 'middle man' tab. I also tried TRIM. Using TRIM & the ISNUMBER(SEARCH methods doesn't give an error, but it gives 0 which is not correct. Thanks!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple sumproduct with dates
maybe...
=SUMPRODUCT(--(tab2!J4:J200=O8), --(text(tab2!C4:C200,"mmm")=e5), --(tab2!AA4:AA200=A23), tab2!O4:O200) As long as you really have text--not a date formatted as to show just the month. And as long as you spelled your month abbreviations the same way excel does when you use =text(a1,"mmm"). creativeops wrote: Hi - I'm having trouble with a sumproduct formula on tab1. =SUMPRODUCT(--(tab2!J4:J200=O8),--(ISNUMBER(SEARCH(E5,tab2!C4:C200))),--(tab2!AA4:AA200=A23),tab2!O4:O200) E5 is a 3-letter month representation (i.e. AUG), which is text, based on another cell on the same tab (tab1) which is a date and formatted as such. Column C on 'tab2' is dates, but, the problem I think is that all the columns on 'tab2' are themselves products of sumproduct formulas pointing to yet another tab (tab3). But, I can't go directly from tab1 to tab3 (tab 3 is a raw data tab), because the formulas would get much more complicated. I use a similar technique on a different document where I can go directly from the 1st tab to the 'raw data' tab, and it works: =SUMPRODUCT(--(tab3!I4:I200=O8),--(MONTH(tab3!B4:B200)=(MONTH(A2))),--(tab3!R4:R200=A23),tab3!O4:O200) In case you're wondering, I had tried the MONTH function first, but that gave a #VALUE error when used on the new document that has a 'middle man' tab. I also tried TRIM. Using TRIM & the ISNUMBER(SEARCH methods doesn't give an error, but it gives 0 which is not correct. Thanks!!! -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple sumproduct with dates
that worked! thanks a lot Dave
"Dave Peterson" wrote: maybe... =SUMPRODUCT(--(tab2!J4:J200=O8), --(text(tab2!C4:C200,"mmm")=e5), --(tab2!AA4:AA200=A23), tab2!O4:O200) As long as you really have text--not a date formatted as to show just the month. And as long as you spelled your month abbreviations the same way excel does when you use =text(a1,"mmm"). creativeops wrote: Hi - I'm having trouble with a sumproduct formula on tab1. =SUMPRODUCT(--(tab2!J4:J200=O8),--(ISNUMBER(SEARCH(E5,tab2!C4:C200))),--(tab2!AA4:AA200=A23),tab2!O4:O200) E5 is a 3-letter month representation (i.e. AUG), which is text, based on another cell on the same tab (tab1) which is a date and formatted as such. Column C on 'tab2' is dates, but, the problem I think is that all the columns on 'tab2' are themselves products of sumproduct formulas pointing to yet another tab (tab3). But, I can't go directly from tab1 to tab3 (tab 3 is a raw data tab), because the formulas would get much more complicated. I use a similar technique on a different document where I can go directly from the 1st tab to the 'raw data' tab, and it works: =SUMPRODUCT(--(tab3!I4:I200=O8),--(MONTH(tab3!B4:B200)=(MONTH(A2))),--(tab3!R4:R200=A23),tab3!O4:O200) In case you're wondering, I had tried the MONTH function first, but that gave a #VALUE error when used on the new document that has a 'middle man' tab. I also tried TRIM. Using TRIM & the ISNUMBER(SEARCH methods doesn't give an error, but it gives 0 which is not correct. Thanks!!! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Retrieve multiple dates, from a week number? | New Users to Excel | |||
SUMPRODUCT on multiple values | Excel Discussion (Misc queries) | |||
Multiple Criteria in SumProduct, N/A Result | Excel Worksheet Functions | |||
looking for a calendar template that I can import multiple dates | Excel Discussion (Misc queries) | |||
Seach Column and return multiple dates to another worksheet? | Excel Worksheet Functions |