Multiple "IF" formula issue

garymu16

Board Regular
Joined
Sep 15, 2006
Messages
139
=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.
 

Some videos you may like

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
Joined
Apr 25, 2006
Messages
19,870
Hi garymu16

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

try

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

HTH
PGC
 

garymu16

Board Regular
Joined
Sep 15, 2006
Messages
139
Hi tried that but I think it could be either the 2nd command or the combination thats throwing it.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
Joined
Apr 25, 2006
Messages
19,870

ADVERTISEMENT

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
Joined
Sep 15, 2006
Messages
139
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
Joined
May 28, 2005
Messages
46,822
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Sep 15, 2006
Messages
139
Still not working! :rolleyes:

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
Joined
Mar 23, 2005
Messages
20,825
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,848
Messages
5,544,640
Members
410,627
Latest member
georgealice
Top