Conditional Sum with "contains"
 

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

Conditional Sum with "contains"
 

Pflugs wrote:
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

Conditional Sum with "contains"
 

Thanks very much!

"Ken Johnson" wrote:


Pflugs wrote:
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

Conditional Sum with "contains"
 

You're welcome.
Thanks for the feedback.

Ken Johnson

Conditional Sum with "contains"
 

I can recommend that you copy all the columns to a single excle sheet. This
is basicall to run an advance query which will fetch you all the information
for you to compare. I shall explain you how to do it.

Assuming that you have copied all the columns to a single spread sheet, you
know the column heading now, save the excel sheet.

Now I wiil be using the Advance filtering techniques so that all the column
information are retrieved even if the typed cells are misspelled, which
normally is not the case when you do a simple filtering technique. The
filtered values may not be correct.

To retieve the values;

1. Select all records and the column headings and define a name for the
range List. This is the List range name.
2. Select all column headings and paste in a seperate row anywhere in the
spreadsheet.
2. Select the pasted column heading along with an empty row and define a
name. This is the criteria range name.
3. Define a criteria on the second row for all columns, if necessary. For
example S* will display all details specific to "S'.
4. Click Data- Filter- Advanced Filter
5. Type the List name
6. Type the Criteria Name
7. Click copy to another location option - Mandatory
8. Click Copy to
9. Click the cell below the criteria range

All data will be displayed specific to the query you requested. This is very
usefull if the spreadsheet data are mistyped or mis-spelled. Now you have
done a database funtion on your excle spread sheet. Add a column to find out
the sum of rows.

Challa Prabhu



"Pflugs" wrote:

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