| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a formula for two columns with Criteria
A B
1 10 20 2 10 0 3 10 -5 4 10 15 5 10 0 Alright say you have this as your data field. I know that using the SUMIF function will give me the total of A if B 0 (or whatever I set the criteria to), BUT how can I get the sum of A AND B if B is greater than 0 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a formula for two columns with Criteria
I'd use something like:
=SUMIF(B:B,""&0,A:A)+SUMIF(B:B,""&0) or =SUMIF(B:B,""&0,A:A)+SUMIF(B:B,""&0,B:B) smorrison wrote: A B 1 10 20 2 10 0 3 10 -5 4 10 15 5 10 0 Alright say you have this as your data field. I know that using the SUMIF function will give me the total of A if B 0 (or whatever I set the criteria to), BUT how can I get the sum of A AND B if B is greater than 0 -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a formula for two columns with Criteria
Try this:
=SUMPRODUCT((B1:B50)*((B1:B5)+(A1:A5))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "smorrison" wrote in message ... A B 1 10 20 2 10 0 3 10 -5 4 10 15 5 10 0 Alright say you have this as your data field. I know that using the SUMIF function will give me the total of A if B 0 (or whatever I set the criteria to), BUT how can I get the sum of A AND B if B is greater than 0 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a formula for two columns with Criteria
Slightly shorter, and more elegant
=SUMPRODUCT((B1:B50)*A1:B5) "Ragdyer" wrote: Try this: =SUMPRODUCT((B1:B50)*((B1:B5)+(A1:A5))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "smorrison" wrote in message ... A B 1 10 20 2 10 0 3 10 -5 4 10 15 5 10 0 Alright say you have this as your data field. I know that using the SUMIF function will give me the total of A if B 0 (or whatever I set the criteria to), BUT how can I get the sum of A AND B if B is greater than 0 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a formula for two columns with Criteria
Yes ... more elegant!<bg
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Teethless mama" wrote in message ... Slightly shorter, and more elegant =SUMPRODUCT((B1:B50)*A1:B5) "Ragdyer" wrote: Try this: =SUMPRODUCT((B1:B50)*((B1:B5)+(A1:A5))) -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "smorrison" wrote in message ... A B 1 10 20 2 10 0 3 10 -5 4 10 15 5 10 0 Alright say you have this as your data field. I know that using the SUMIF function will give me the total of A if B 0 (or whatever I set the criteria to), BUT how can I get the sum of A AND B if B is greater than 0 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how can I have a formula result based on multiple criteria/columns | New Users to Excel | |||
| Creating shapes from given criteria | Excel Worksheet Functions | |||
| Need formula that Counts items matching criteria using two columns | Excel Worksheet Functions | |||
| formula to count occurence of criteria in 2 columns | Excel Worksheet Functions | |||
| Pivot Table Creating New Columns that Subtract Two Existing Columns | Excel Discussion (Misc queries) | |||
Linear Mode
