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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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,884

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
50,509
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
 

Forum statistics

Threads
1,141,011
Messages
5,703,724
Members
421,311
Latest member
tanujath

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top