# Multiple "IF" formula issue

#### garymu16

##### Board Regular
=sum(IF((Pipeline!A2:A65000,\$G\$6)*(Pipeline!G2:G65000>79), Pipeline!D2:D65000))

Can anyone see where I'm going wrong here?

The first two commands I'm trying to establish are

1. Does the name match \$G\$6
And
2. Does it also have a probability higher than 79

If so sum range D2:D65000 for all info that meets the two criteria.

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hi garymu16

... (Pipeline!A2:A65000,\$G\$6) ...

try

... (Pipeline!A2:A65000=\$G\$6) ...

HTH
PGC

Hi tried that but I think it could be either the 2nd command or the combination thats throwing it.

Hi garymu16:

If I understand you correctly, the following should work for you ...

array formula =SUM(IF(Pipeline!A2:A6500=G6,IF(G6>79,Pipeline!D2:D6500)))

or

=SUMPRODUCT((Pipeline!A2:A6500=G6)*(G6>79),Pipeline!D2:D6500)

Hi again

=sum(IF((Pipeline!A2:A65000,\$G\$6)*(Pipeline!G2:G65000>79), Pipeline!D2:D65000))

1. Does the name match \$G\$6
And
2. Does it also have a probability higher than 79

I was reading your first post nd one thing is not clear to me. I'll try to explain:

Pipeline!G2:G65000>79 must refer to "Does it also have a probability higher than 79 ".
This indicates that the column G is probabilities.

therefore

Pipeline!A2:A65000,\$G\$6 must refer to "Does the name match \$G\$6 "
This indicates that the column A has names.

How are you testing the names against \$G\$6, that is one of the probabilities?

Am I missing something?
PGC

Hi pgc01,

Sorry maybe my initial post was a little unclear. \$G\$6 is a name, however it is on a different worksheet from Pipeline! which as you correctly point out uses column G to track probability.

Hope this is a bit clearer - Really appreciate your help guys. I need to pull this together for the powers that be and I'm struggling.

garymu16

Is this it?
Code:
``=SUMPRODUCT(--(Pipeline!A2:A65000=\$G\$6),--(Pipeline!G2:G65000>79),Pipeline!D2:D65000)``

.. or perhaps this?
Code:
``=SUMPRODUCT(--(Pipeline!A2:A65000=OtherSheetname!\$G\$6),--(Pipeline!G2:G65000>79),Pipeline!D2:D65000)``

Still not working!

Sorry, I'm sure it's me. How would you write a formula to have 2 "if" elements on a single sheet?

As an example could someone let me know how I would work out the following:
Say A1 and B1 equalled 1 & 2 respectively how would I write the formula to say if A1 = 1 and B1 = 2 then sum A1.

Hi Gary,

From the information you've given Peter's first SUMPRODUCT formula looks right to me. Why doesn't that work, what result do you get?

In what format are your probabilities? If they're percentages then use Peter's formula but change 79 to 0.79

When I try the formula the result it gives is #REF!

Replies
4
Views
379
Replies
7
Views
533
Replies
4
Views
425
Replies
3
Views
275
Replies
3
Views
234

1,219,997
Messages
6,151,361
Members
451,022
Latest member
Baijano23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back