Multiple "IF" formula issue

[garymu16]

=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.

 

[pgc01]

Hi garymu16

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

try

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

HTH
PGC

 

[garymu16]

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

 

[Yogi Anand]

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]

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]

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]

garymu16

Is this it?

=SUMPRODUCT(--(Pipeline!A2:A65000=$G$6),--(Pipeline!G2:G65000>79),Pipeline!D2:D65000)

.. or perhaps this?

=SUMPRODUCT(--(Pipeline!A2:A65000=OtherSheetname!$G$6),--(Pipeline!G2:G65000>79),Pipeline!D2:D65000)

 

[garymu16]

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]

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]

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