Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct using 3 columns and using (LEFT wildcard)
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct using 3 columns and using (LEFT wildcard)
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) wrote in message ups.com... 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(Equa lsDev,2)="UD"),--(LEFT(ForwardINV01,5)="INV01")) Any suggestions on correcting this or simplifying this would be great. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct using 3 columns and using (LEFT wildcard)
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct using 3 columns and using (LEFT wildcard)
Correction, I'm getting a #VALUE error message
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct using 3 columns and using (LEFT wildcard)
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wildcard in SumProduct | Excel Worksheet Functions | |||
Wildcard and Sumproduct | Excel Worksheet Functions | |||
Sumproduct + wildcard | Excel Worksheet Functions | |||
SUMPRODUCT with Wildcard | Excel Worksheet Functions | |||
Sumproduct Wildcard | Excel Discussion (Misc queries) |