Sumproduct for partial name?

  • Thread starter Thread starter wx4usa
  • Start date Start date
W

wx4usa

I have a chart of accounts. All accounts have a hyphen separating the
main account and sub account. I have a sumproduct formula that
returns the sum of all main & sub accounts such as Advertising-
Billboards. But I need a sumproduct formula to return the total of
all Advertising too. A formula that looks at the main account BEFORE
the hyphen. Is that possible?

Advertising-Radio
Advertising-ROP
Advertising-Direct Mail
Advertising-Billboards
Advertising-TV
Advertising-Broadcast
 
Hi

This should do it:

=SUMPRODUCT(--(LEFT(A1:A100,11)="Advertising"),B1:B100)

Regards,
Per
 
Hi, You should be able to use the 'wildcard characters' specifficly the * one
sounds like the one that you'll need in your formula
 
Not in =sumproduct() formulas.

But if that formula isn't checking multiple criteria, then maybe =sumif() would
work:

=sumif(a:a,"advertising*",b:b)

In xl2007, =sumifs() may even do everything the user wants. But without knowing
the original formula, that's just a guess.
 
Back
Top