Sum of results of multiple criterias

[Alex]

Say I have a report sent to me as below:-

----------------------------------------------
A B C
----------------------------------------------
Emile Heskey ASSL1NP 12
Wayne Rooney ABCV1NP 8
Peter Crouch ABCV1NP 3
Wayne Gerrard ASSL1NP 5
Martin Crouch ASSL1NP 10
Ryan Heskey ABCV1NP 7
Crouch Tiger ASSL2AM 4
----------------------------------------------

Now I want to create a table which automatically find name that contain
Crouch AND also contain the letters "SSL" in his tag in column B, and then
sum the corresponding numbers in column C. I need to do this also for other
names and tabulate them in a table. Help please.

 

[Ashish Mathur]

Hi,

You may use this formula. C12 holds Crouch and D12 holds SSL. I have
assumed that the range below is C4:E10

=SUMPRODUCT((ISNUMBER(SEARCH(C12,C4:C10,1)))*ISNUMBER(SEARCH(D12,D410,1)),E4:E10)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ron Rosenfeld]

Say I have a report sent to me as below:-

----------------------------------------------
A B C
----------------------------------------------
Emile Heskey ASSL1NP 12
Wayne Rooney ABCV1NP 8
Peter Crouch ABCV1NP 3
Wayne Gerrard ASSL1NP 5
Martin Crouch ASSL1NP 10
Ryan Heskey ABCV1NP 7
Crouch Tiger ASSL2AM 4
----------------------------------------------

Now I want to create a table which automatically find name that contain
Crouch AND also contain the letters "SSL" in his tag in column B, and then
sum the corresponding numbers in column C. I need to do this also for other
names and tabulate them in a table. Help please.

One method is to use the Advanced Filter (under the Data menu or ribbon)

For example
Name your three columns. I used Name Code Count

Some place on your workbook set up a criteria range that looks like:

Name Code
*Crouch* *SSL*

Select some cell in your data table. Then Data/Advanced Filter

In the dialog box ensure your list range is correct. Then select the criteria
range and, optionally, a separate area to have the results (Copy to.

You can change the "Copy to:" range for differing criteria.

Given your criteria, I obtained this table:

Name Code Count
Martin Crouch ASSL1NP 10
Crouch Tiger ASSL2AM 4


--ron

 

[Herbert Seidenberg]

Excel 2007 Pivot Table
No formulas.
Plain English nudges.
Automatic sum.
Lots more options.
http://www.mediafire.com/file/jc42lztkzlz/04_02_09a.xlsx