Excel - IF formula with range of cells

C

Chris

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)))
 
M

Mike H

Chris,

Try this

=SUMPRODUCT((A2:A500="Value1")*(B2:B500="Value2")*(C2:C500))

Mike
 
R

ryguy7272

Here's another way:
=SUMPRODUCT(--(A1:A13="vijayawada1"),--(B1:B13="vijayawada2"),C1:C13)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top