Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum column 3 if columns 1 and 2 meet criteria
Hi,
I have two worksheets and need to show the totals on worksheet 1 when the criteria from worksheet 2 are met. For instance, on worksheet 2, when column A = 1 and column B = Y, then sum C. In this example, the amount I expect on worksheet 1 is 20.00 Worksheet 1 A B 1Y $20.00 1N $10.00 2Y $10.00 2N $0 Worksheet 2 A B C 1 Y 10.00 1 N 10.00 2 Y 10.00 1 Y 10.00 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum column 3 if columns 1 and 2 meet criteria
On sheet1 you should use separate cells for the number and letter:
......A.....B.....C 1...1.....Y.....20 Enter this formula in C1: =SUMPRODUCT(--(Sheet2!A$1:A$4=A1),--(Sheet2!B$1:B$4=B1),Sheet2!C$1:C$4) Copy down as needed. -- Biff Microsoft Excel MVP "bokey" wrote in message ... Hi, I have two worksheets and need to show the totals on worksheet 1 when the criteria from worksheet 2 are met. For instance, on worksheet 2, when column A = 1 and column B = Y, then sum C. In this example, the amount I expect on worksheet 1 is 20.00 Worksheet 1 A B 1Y $20.00 1N $10.00 2Y $10.00 2N $0 Worksheet 2 A B C 1 Y 10.00 1 N 10.00 2 Y 10.00 1 Y 10.00 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum column 3 if columns 1 and 2 meet criteria
It's a bit more complicated than I illustrated. On worksheet 2, cloumn 1 represents different phases and column 2 represents different catagories with column 3 being the a sum of the total amount paid to date. Worksheet 1 looks like this: A B C Design Phase Development Conversion Expense Implementation Phase Development Inteface Fees "T. Valko" wrote: On sheet1 you should use separate cells for the number and letter: ......A.....B.....C 1...1.....Y.....20 Enter this formula in C1: =SUMPRODUCT(--(Sheet2!A$1:A$4=A1),--(Sheet2!B$1:B$4=B1),Sheet2!C$1:C$4) Copy down as needed. -- Biff Microsoft Excel MVP "bokey" wrote in message ... Hi, I have two worksheets and need to show the totals on worksheet 1 when the criteria from worksheet 2 are met. For instance, on worksheet 2, when column A = 1 and column B = Y, then sum C. In this example, the amount I expect on worksheet 1 is 20.00 Worksheet 1 A B 1Y $20.00 1N $10.00 2Y $10.00 2N $0 Worksheet 2 A B C 1 Y 10.00 1 N 10.00 2 Y 10.00 1 Y 10.00 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum column 3 if columns 1 and 2 meet criteria
Ok, now I'm lost!
I don't see how the first example relates to the second example at all. -- Biff Microsoft Excel MVP "bokey" wrote in message ... It's a bit more complicated than I illustrated. On worksheet 2, cloumn 1 represents different phases and column 2 represents different catagories with column 3 being the a sum of the total amount paid to date. Worksheet 1 looks like this: A B C Design Phase Development Conversion Expense Implementation Phase Development Inteface Fees "T. Valko" wrote: On sheet1 you should use separate cells for the number and letter: ......A.....B.....C 1...1.....Y.....20 Enter this formula in C1: =SUMPRODUCT(--(Sheet2!A$1:A$4=A1),--(Sheet2!B$1:B$4=B1),Sheet2!C$1:C$4) Copy down as needed. -- Biff Microsoft Excel MVP "bokey" wrote in message ... Hi, I have two worksheets and need to show the totals on worksheet 1 when the criteria from worksheet 2 are met. For instance, on worksheet 2, when column A = 1 and column B = Y, then sum C. In this example, the amount I expect on worksheet 1 is 20.00 Worksheet 1 A B 1Y $20.00 1N $10.00 2Y $10.00 2N $0 Worksheet 2 A B C 1 Y 10.00 1 N 10.00 2 Y 10.00 1 Y 10.00 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum results from 2 columns that meet criteria | Excel Worksheet Functions | |||
Counting # of cells with that meet criteria in two columns | Excel Worksheet Functions | |||
How do I count 2 columns of data that meet a certain criteria? | Excel Discussion (Misc queries) | |||
count rows that meet two criteria in two different columns? | Excel Worksheet Functions | |||
If 2 cols meet a criteria then sum the 3rd column | Excel Worksheet Functions |