# 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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### pgc01

##### MrExcel MVP
Hi garymu16

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

try

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

HTH
PGC

#### garymu16

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

#### Yogi Anand

##### MrExcel MVP
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)

#### pgc01

##### MrExcel MVP

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

#### garymu16

##### Board Regular
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.

#### Peter_SSs

##### MrExcel MVP, Moderator

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

#### garymu16

##### Board Regular
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.

#### barry houdini

##### MrExcel MVP
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

#### garymu16

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

Replies
13
Views
254
Replies
1
Views
182
Replies
3
Views
119
Replies
4
Views
209
Replies
5
Views
71