Conditional Sum with "contains"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My mom's financial spreadsheet includes an "amount" and an "info" column that
contains values such as "cc" and "cc (something)." She'd like to find the
total of all rows that contain "cc" in the info column, but she wants to
include the "cc (something)" entry.

I've used the conditional sum wizard, but is there a way to make it work
with a "contains" criteria? The other alternative is to use autofilter and
sum individually, but I'd like a formula-based solution.

Thanks,
Pflugs
 
Pflugs said:
My mom's financial spreadsheet includes an "amount" and an "info" column that
contains values such as "cc" and "cc (something)." She'd like to find the
total of all rows that contain "cc" in the info column, but she wants to
include the "cc (something)" entry.

I've used the conditional sum wizard, but is there a way to make it work
with a "contains" criteria? The other alternative is to use autofilter and
sum individually, but I'd like a formula-based solution.

Thanks,
Pflugs
Click to expand...

You could try SUMPRODUCT

Say amounts are in A1:A6 and info is in B1:B6, then...

=SUMPRODUCT(A1:A6*(LEFT(B1:B6,2)="cc"))

seems to work.

Ken Johnson
 
Thanks very much!

Ken Johnson said:
You could try SUMPRODUCT

Say amounts are in A1:A6 and info is in B1:B6, then...

=SUMPRODUCT(A1:A6*(LEFT(B1:B6,2)="cc"))

seems to work.

Ken Johnson
Click to expand...
 
Back
Top