| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding up sums only if condition is met
Here's an example of a spread sheet:
A B 1 1 $55.25 2 $997.00 3 $693.36 4 1 $535.00 5 1 $325.12 6 $636.36 7 1 $1009.00 can someone help me with an function to help me add up the total of coloumn B only if coloumn A has a "1" I tried this: =SUMPRODUCT(--(Sheet1!A1:A7="1"),Sheet1!B1:B7) and it wasn't working, i think this is close though |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding up sums only if condition is met
I tried this: =SUMPRODUCT(--(Sheet1!A1:A7="1"),Sheet1!B1:B7)
and it wasn't working, i think this is close though Pretty close! Just remove the quotes from around 1: =SUMPRODUCT(--(Sheet1!A1:A7=1),Sheet1!B1:B7) Here's a couple of alternatives... If column A contains only the number 1 or blank/empty cells: =SUMPRODUCT(Sheet1!A1:A7,Sheet1!B1:B7) This is the best option: =SUMIF(Sheet1!A1:A7,1,Sheet1!B1:B7) -- Biff Microsoft Excel MVP "Doc Behr" <Doc wrote in message ... Here's an example of a spread sheet: A B 1 1 $55.25 2 $997.00 3 $693.36 4 1 $535.00 5 1 $325.12 6 $636.36 7 1 $1009.00 can someone help me with an function to help me add up the total of coloumn B only if coloumn A has a "1" I tried this: =SUMPRODUCT(--(Sheet1!A1:A7="1"),Sheet1!B1:B7) and it wasn't working, i think this is close though |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding up sums only if condition is met
Awsome, thanks for all the helpful hints, now i have one more question
related to this. how do i search for 2 variables in two coloumns and then add up the sum of the 3rd coloumn. For example: A B C 1 1 $55.25 AJ 2 $997.00 BM 3 $693.36 BM 4 1 $535.00 CF 5 1 $325.12 AJ 6 $636.36 CF 7 1 $1009.00 CF how would i add on to this formula to only add up coloumn B if in coloumn A there is a "1" and in coloumn C there is a "AJ"? "T. Valko" wrote: I tried this: =SUMPRODUCT(--(Sheet1!A1:A7="1"),Sheet1!B1:B7) and it wasn't working, i think this is close though Pretty close! Just remove the quotes from around 1: =SUMPRODUCT(--(Sheet1!A1:A7=1),Sheet1!B1:B7) Here's a couple of alternatives... If column A contains only the number 1 or blank/empty cells: =SUMPRODUCT(Sheet1!A1:A7,Sheet1!B1:B7) This is the best option: =SUMIF(Sheet1!A1:A7,1,Sheet1!B1:B7) -- Biff Microsoft Excel MVP "Doc Behr" <Doc wrote in message ... Here's an example of a spread sheet: A B 1 1 $55.25 2 $997.00 3 $693.36 4 1 $535.00 5 1 $325.12 6 $636.36 7 1 $1009.00 can someone help me with an function to help me add up the total of coloumn B only if coloumn A has a "1" I tried this: =SUMPRODUCT(--(Sheet1!A1:A7="1"),Sheet1!B1:B7) and it wasn't working, i think this is close though |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding up sums only if condition is met
Try this:
=SUMPRODUCT(--(Sheet1!A1:A7=1),--(Sheet1!A1:A7="AJ"),Sheet1!B1:B7) Better to use cells to hold the criteria: A1 = 1 B1 = AJ =SUMPRODUCT(--(Sheet1!A1:A7=A1),--(Sheet1!A1:A7=B1),Sheet1!B1:B7) If you're using Excel 2007: =SUMIFS(Sheet1!B1:B7,Sheet1!A1:A7,A1,Sheet1!C1:C7, B1) -- Biff Microsoft Excel MVP "Doc Behr" wrote in message ... Awsome, thanks for all the helpful hints, now i have one more question related to this. how do i search for 2 variables in two coloumns and then add up the sum of the 3rd coloumn. For example: A B C 1 1 $55.25 AJ 2 $997.00 BM 3 $693.36 BM 4 1 $535.00 CF 5 1 $325.12 AJ 6 $636.36 CF 7 1 $1009.00 CF how would i add on to this formula to only add up coloumn B if in coloumn A there is a "1" and in coloumn C there is a "AJ"? "T. Valko" wrote: I tried this: =SUMPRODUCT(--(Sheet1!A1:A7="1"),Sheet1!B1:B7) and it wasn't working, i think this is close though Pretty close! Just remove the quotes from around 1: =SUMPRODUCT(--(Sheet1!A1:A7=1),Sheet1!B1:B7) Here's a couple of alternatives... If column A contains only the number 1 or blank/empty cells: =SUMPRODUCT(Sheet1!A1:A7,Sheet1!B1:B7) This is the best option: =SUMIF(Sheet1!A1:A7,1,Sheet1!B1:B7) -- Biff Microsoft Excel MVP "Doc Behr" <Doc wrote in message ... Here's an example of a spread sheet: A B 1 1 $55.25 2 $997.00 3 $693.36 4 1 $535.00 5 1 $325.12 6 $636.36 7 1 $1009.00 can someone help me with an function to help me add up the total of coloumn B only if coloumn A has a "1" I tried this: =SUMPRODUCT(--(Sheet1!A1:A7="1"),Sheet1!B1:B7) and it wasn't working, i think this is close though |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding up sums only if condition is met
No disrespect intended, BUT, after Biff showed you the correction to *your*
original Sumproduct formula, you can't figure this out for yourself? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Doc Behr" wrote in message ... Awsome, thanks for all the helpful hints, now i have one more question related to this. how do i search for 2 variables in two coloumns and then add up the sum of the 3rd coloumn. For example: A B C 1 1 $55.25 AJ 2 $997.00 BM 3 $693.36 BM 4 1 $535.00 CF 5 1 $325.12 AJ 6 $636.36 CF 7 1 $1009.00 CF how would i add on to this formula to only add up coloumn B if in coloumn A there is a "1" and in coloumn C there is a "AJ"? "T. Valko" wrote: I tried this: =SUMPRODUCT(--(Sheet1!A1:A7="1"),Sheet1!B1:B7) and it wasn't working, i think this is close though Pretty close! Just remove the quotes from around 1: =SUMPRODUCT(--(Sheet1!A1:A7=1),Sheet1!B1:B7) Here's a couple of alternatives... If column A contains only the number 1 or blank/empty cells: =SUMPRODUCT(Sheet1!A1:A7,Sheet1!B1:B7) This is the best option: =SUMIF(Sheet1!A1:A7,1,Sheet1!B1:B7) -- Biff Microsoft Excel MVP "Doc Behr" <Doc wrote in message ... Here's an example of a spread sheet: A B 1 1 $55.25 2 $997.00 3 $693.36 4 1 $535.00 5 1 $325.12 6 $636.36 7 1 $1009.00 can someone help me with an function to help me add up the total of coloumn B only if coloumn A has a "1" I tried this: =SUMPRODUCT(--(Sheet1!A1:A7="1"),Sheet1!B1:B7) and it wasn't working, i think this is close though |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding up sums only if condition is met
Ooops!
"typo"....that's what I get for copying/pasting! =SUMPRODUCT(--(Sheet1!A1:A7=1),--(Sheet1!A1:A7="AJ"),Sheet1!B1:B7) Should be: =SUMPRODUCT(--(Sheet1!A1:A7=1),--(Sheet1!C1:C7="AJ"),Sheet1!B1:B7) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this: =SUMPRODUCT(--(Sheet1!A1:A7=1),--(Sheet1!A1:A7="AJ"),Sheet1!B1:B7) Better to use cells to hold the criteria: A1 = 1 B1 = AJ =SUMPRODUCT(--(Sheet1!A1:A7=A1),--(Sheet1!A1:A7=B1),Sheet1!B1:B7) If you're using Excel 2007: =SUMIFS(Sheet1!B1:B7,Sheet1!A1:A7,A1,Sheet1!C1:C7, B1) -- Biff Microsoft Excel MVP "Doc Behr" wrote in message ... Awsome, thanks for all the helpful hints, now i have one more question related to this. how do i search for 2 variables in two coloumns and then add up the sum of the 3rd coloumn. For example: A B C 1 1 $55.25 AJ 2 $997.00 BM 3 $693.36 BM 4 1 $535.00 CF 5 1 $325.12 AJ 6 $636.36 CF 7 1 $1009.00 CF how would i add on to this formula to only add up coloumn B if in coloumn A there is a "1" and in coloumn C there is a "AJ"? "T. Valko" wrote: I tried this: =SUMPRODUCT(--(Sheet1!A1:A7="1"),Sheet1!B1:B7) and it wasn't working, i think this is close though Pretty close! Just remove the quotes from around 1: =SUMPRODUCT(--(Sheet1!A1:A7=1),Sheet1!B1:B7) Here's a couple of alternatives... If column A contains only the number 1 or blank/empty cells: =SUMPRODUCT(Sheet1!A1:A7,Sheet1!B1:B7) This is the best option: =SUMIF(Sheet1!A1:A7,1,Sheet1!B1:B7) -- Biff Microsoft Excel MVP "Doc Behr" <Doc wrote in message ... Here's an example of a spread sheet: A B 1 1 $55.25 2 $997.00 3 $693.36 4 1 $535.00 5 1 $325.12 6 $636.36 7 1 $1009.00 can someone help me with an function to help me add up the total of coloumn B only if coloumn A has a "1" I tried this: =SUMPRODUCT(--(Sheet1!A1:A7="1"),Sheet1!B1:B7) and it wasn't working, i think this is close though |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding up sums only if condition is met
Hi Doc,
I recommend reading Chip Pearsons pages about formulas. http://www.cpearson.com/excel/MainPage.aspx (search formula) and you will find lot of examples on how to use conditions in formulas. Wkr, JP "Doc Behr" <Doc wrote in message ... Here's an example of a spread sheet: A B 1 1 $55.25 2 $997.00 3 $693.36 4 1 $535.00 5 1 $325.12 6 $636.36 7 1 $1009.00 can someone help me with an function to help me add up the total of coloumn B only if coloumn A has a "1" I tried this: =SUMPRODUCT(--(Sheet1!A1:A7="1"),Sheet1!B1:B7) and it wasn't working, i think this is close though |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding sums in rows and columns and colouring cells with condition | Excel Discussion (Misc queries) | |||
| Formula for adding sums | Excel Worksheet Functions | |||
| Adding sums from different worksheets | Excel Worksheet Functions | |||
| Finding a value by adding certain sums in a range? | Excel Discussion (Misc queries) | |||
| Adding up Sums | Excel Worksheet Functions | |||
Linear Mode
