I am struggling with sumproduct, pls help

**edwardlobo** · 02-25-2013, 07:02 AM

I wonder if someone cld help me with this formula

My current formula that works is
=sumproduct((D10:D100)*(left(f10:f100,3)={"app","ora","cof"}))

I want to make app, ora, cof dynamic by putting them into cells and linking that cell something like this
=sumproduct((D10:D100)*(left(f10:f100,3)=e1:e3))

I tried various ways to achieve this but nothing worked.

I would be very appreciative if anyone cld guide me on how to make this working or if this is something not doable. I searched high and low on google to get this working

Many thanks for looking

**martindwilson** · 02-25-2013, 07:10 AM

=sumproduct((d10:d100)*((left(f10:f100,3)=e1)+(left(f10:f100,3)=e2)+(left(f10:f100,3)=e3)))

**edwardlobo** · 02-25-2013, 07:23 AM

Thanks Martin
Is that the only way ?
I have around 50 criteria as I am trying to take 20 departments and around 2000 gl codes and spreading them over 6 company profit & loss accounts.
I was thinking there might be a better way

**daddylonglegs** · 02-25-2013, 07:24 AM

Try this version - you can expand E1:E3 to be as large as you want

=SUMPRODUCT(SUMIF(F10:F100,E1:E3&"*",D10:D100))

**edwardlobo** · 02-25-2013, 07:46 AM

Thank you very much daddyloglegs.

I am sorry I have wasted your time

The solution works perfectly but I have 2 criteria.

In my ignorance I thought I will put a simple formula. The formula I am working on is
=SUMPRODUCT(('Account Analysis'!D10:D9999)*(LEFT('Account Analysis'!F10:F9999,3)={"402","411"})*(RIGHT('Account Analysis'!I10:I9999,2)={"31","23"}))

The solution you gave does work but I dont know how to make it work for more than one criteria.

I am sorry. Thanks for your help

**daddylonglegs** · 02-25-2013, 08:13 AM

OK then - the simplest way is probably to use ISNUMBER/MATCH so that would make this formula

=SUMPRODUCT('Account Analysis'!D10:D9999,ISNUMBER(MATCH(LEFT('Account Analysis'!F10:F9999,3),{"402","411"},0))+0,ISNUMBER(MATCH(RIGHT('Account Analysis'!I10:I9999,2),{"31","23"},0))+0)

You can now replace the two parts with curly braces, i.e. {"402","411"} and {"31","23"} with any single column/row ranges that contain the criteria, e.g.

=SUMPRODUCT('Account Analysis'!D10:D9999,ISNUMBER(MATCH(LEFT('Account Analysis'!F10:F9999,3),E1:E50,0))+0,ISNUMBER(MATCH(RIGHT('Account Analysis'!I10:I9999,2),A1:Z1,0))+0)

Note: that in your version the formula will only count 402/31 and 411/23 combinations and no others

**edwardlobo** · 02-25-2013, 08:19 AM

Thank you so much !!
This works perfectly well
I can't thank you enough !
I was struggling for about 2-3 days on google to get this working.
Have a wonderful day daddylonglegs