|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
January 7th, 2010, 11:45 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel - IF formula with range of cells
I am trying to create a formula that if a range of cells contains a specific
value AND a range of cells in the next column contains a specific value, then the corresponding cells in the third column will be summed. What I have is below. It recognizes the formula as long as all the cells in the range of the first two columns contain the respective specific values. Can anyone suggest a corrected formula, so the values in the cell ranges can vary? Thanks! =(IF((AND(A2:A500="Value 1",B2:B500="Value 2")),SUM(C2:C500))) 
|
|
#2
January 8th, 2010, 12:01 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel - IF formula with range of cells
Chris,
Try this =SUMPRODUCT((A2:A500="Value1")*(B2:B500="Value2")* (C2:C500)) Mike "Chris" wrote: I am trying to create a formula that if a range of cells contains a specific value AND a range of cells in the next column contains a specific value, then the corresponding cells in the third column will be summed. What I have is below. It recognizes the formula as long as all the cells in the range of the first two columns contain the respective specific values. Can anyone suggest a corrected formula, so the values in the cell ranges can vary? Thanks! =(IF((AND(A2:A500="Value 1",B2:B500="Value 2")),SUM(C2:C500)))
|
|
#3
January 8th, 2010, 12:24 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel - IF formula with range of cells
Here's another way:
=SUMPRODUCT(--(A1:A13="vijayawada1"),--(B1:B13="vijayawada2"),C1:C13) -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Mike H" wrote: Chris, Try this =SUMPRODUCT((A2:A500="Value1")*(B2:B500="Value2")* (C2:C500)) Mike "Chris" wrote: I am trying to create a formula that if a range of cells contains a specific value AND a range of cells in the next column contains a specific value, then the corresponding cells in the third column will be summed. What I have is below. It recognizes the formula as long as all the cells in the range of the first two columns contain the respective specific values. Can anyone suggest a corrected formula, so the values in the cell ranges can vary? Thanks! =(IF((AND(A2:A500="Value 1",B2:B500="Value 2")),SUM(C2:C500)))
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
