Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of results of multiple criterias
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of results of multiple criterias
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)))*ISNUM BER(SEARCH(D12,D4:D10,1)),E4:E10) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Alex" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of results of multiple criterias
On Thu, 2 Apr 2009 03:41:14 -0700, Alex wrote:
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of results of multiple criterias
Excel 2007 Pivot Table
No formulas. Plain English nudges. Automatic sum. Lots more options. http://www.mediafire.com/file/jc42lz...04_02_09a.xlsx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count with multiple criterias | Excel Worksheet Functions | |||
sumif with multiple criterias | Excel Worksheet Functions | |||
DCOUNT WITH MULTIPLE CRITERIAS | Excel Worksheet Functions | |||
countif help for multiple criterias | Excel Worksheet Functions | |||
Multiple Data Validation Criterias | Excel Worksheet Functions |