multiple sumproduct with dates

G

Guest

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!!!
 
D

Dave Peterson

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").
 
G

Guest

that worked! thanks a lot Dave

Dave Peterson said:
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").
Click to expand...
 
G

Guest

that worked! thanks a lot Dave

Dave Peterson said:
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").
Click to expand...
 
Top