sumproduct using 3 columns and using (LEFT wildcard)

[mike.wilson8]

I'm trying to see if specific data from 3 columns exist and if so,
count it. I'm using defined name ranges instead of using the ranges in
the formulas.

What I'm trying to do is:
See if group "GSC" is true (Name range = Forward)
See if any word that starts with D or UD is true (Name range =
EqualsDev)
See if any word that starts with INV01 is true (Name range =
ForwardINV01)

Here's my attempt...

=SUMPRODUCT((Forward="GSC")*(LEFT(EqualsDev)="D")+(Forward="GSC")*(LEFT(EqualsDev,2)="UD"),--(LEFT(ForwardINV01,5)="INV01"))

Any suggestions on correcting this or simplifying this would be great.

 

[Bob Phillips]

Works fine but can be simplified slightly

=SUMPRODUCT(--(Forward="GSC"),--((LEFT(EqualsDev,1)="D")+(LEFT(EqualsDev,2)=
"UD")),--(LEFT(ForwardINV01,5)="INV01"))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[mike.wilson8]

Thank you very much...this is going to seem odd, but I was recieving a
#N/A on my formula on the dataset I had, but when I added a test row to
the bottom, it calculated everything fine, it's almost as if the last
row kicked the formula off, but the formula couldn't calculate the
original datase without me adding that last row...has anyone seen that?

 

[mike.wilson8]

Correction, I'm getting a #VALUE error message

 

[mike.wilson8]

I figured it out on my own..the last row had a number of 2006 in a
column where I was looking for only letters. I think that's it.