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!!! |
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 |
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 |
All times are GMT +1. The time now is 05:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com