Prev: Calculating length of time with conditions
Next: Subtotals in Excel 2007 TABLES
From: Dax Arroway on 16 Dec 2009 18:00
I have a long formula that I'm trying to modify:

{=IF(OR(I3>TODAY(),I3=""),"",SUMPRODUCT((Client_Data!$A$3:$A$4000<=$B3)*((Client_Data!$N$3:$N$4000="")+(Client_Data!$N$3:$N$4000>=$B3))*(Client_Data!$K$3:$K$4000=K$2)))}

Row 2 includes headers and this formula pulls data from the Client_Data
sheet which matches the K header. What I'm trying to do is to rewrite this
formula so that it doesn't look at the header's text but can be any text
found on the sheet.

I'm looking for the wildcard character to say "any text found in matching
cells". My guess is, "*", but when I enter the formula:

{=IF(OR(I3>TODAY(),I3=""),"",SUMPRODUCT((Client_Data!$A$3:$A$4000<=$B3)*((Client_Data!$N$3:$N$4000="")+(Client_Data!$N$3:$N$4000>=$B3))*(Client_Data!$K$3:$K$4000="*")))}

It doesn't work the way it should. Can someone please help me exchange the
K$2 entry please??? Seems simple but it's eluding me.

Thanks in advance!
--Dax
--
I would give my left hand to be ambidextrous!
From: Tom Hutchins on 16 Dec 2009 21:03
Trusting that your formula otherwise does what you want, try

=IF(OR(I3>TODAY(),I3=""),"",SUMPRODUCT((Client_Data!$A$3:$A$4000<=$B3)*((Client_Data!$N$3:$N$4000="")+(Client_Data!$N$3:$N$4000>=$B3))*(LEN(Client_Data!$K$3:$K$4000)>0)))

This does NOT need to be array-entered with Ctrl-Shift-Enter.

Hope this helps,

Hutch

"Dax Arroway" wrote:

> I have a long formula that I'm trying to modify:
>
> {=IF(OR(I3>TODAY(),I3=""),"",SUMPRODUCT((Client_Data!$A$3:$A$4000<=$B3)*((Client_Data!$N$3:$N$4000="")+(Client_Data!$N$3:$N$4000>=$B3))*(Client_Data!$K$3:$K$4000=K$2)))}
>
> Row 2 includes headers and this formula pulls data from the Client_Data
> sheet which matches the K header. What I'm trying to do is to rewrite this
> formula so that it doesn't look at the header's text but can be any text
> found on the sheet.
>
> I'm looking for the wildcard character to say "any text found in matching
> cells". My guess is, "*", but when I enter the formula:
>
> {=IF(OR(I3>TODAY(),I3=""),"",SUMPRODUCT((Client_Data!$A$3:$A$4000<=$B3)*((Client_Data!$N$3:$N$4000="")+(Client_Data!$N$3:$N$4000>=$B3))*(Client_Data!$K$3:$K$4000="*")))}
>
> It doesn't work the way it should. Can someone please help me exchange the
> K$2 entry please??? Seems simple but it's eluding me.
>
> Thanks in advance!
> --Dax
> --
> I would give my left hand to be ambidextrous!
 | 
Pages: 1
Prev: Calculating length of time with conditions
Next: Subtotals in Excel 2007 TABLES