Conditional Sum with "contains"

[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

 

[Ken Johnson]

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

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

 

[Guest]

Thanks very much!

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

 

[Ken Johnson]

You're welcome.
Thanks for the feedback.

Ken Johnson