Results 1 to 8 of 8
Thread: Sumif (2000)

20050613, 15:57 #1
 Join Date
 Jun 2005
 Posts
 24
 Thanks
 0
 Thanked 0 Times in 0 Posts
Sumif (2000)
I have used your lounge previously and found it very useful. I currently have, what I am sure is an extremely simple problem to solve, but am finding it frustrating as I cannot think of a solution.
I wish to sum cells J8:L8 and deduct the sum cells G8:I8 if the contents in cell I6 is contained in the range E14:E25 and put the result in cell I23.
Any help would be gratefully received.

20050613, 16:15 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Sumif (2000)
Welcome to Woody's Lounge!
Does the following formula do what you want?
<code>
=SUM(J8:L8)IF(NOT(ISNA(MATCH(I6,E14:E25,0))),SUM(G8:I8))
</code>
MATCH(I6,E14:E25,0) checks if the value of I6 can be found in E14:E25, it returns an index number if found, or #N/A if not. NOT(ISNA(MATCH(I6,E14:E25,0))) is TRUE if the value was found, FALSE if not.

20050613, 16:21 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Sumif (2000)
I am not exactly clear what you want, here is a guess. Put this in I23
<pre>=SUM(J8:L8)ISNUMBER(MATCH(I6,E14:E25,0))*SUM(G8:I8)</pre>
It sums the values in J8:L8. If the contents of I6 is in the range E14:E25, it will subtract the sum of G8:I8, otherwise it will just be the sum of J8:L8.
Steve

20050613, 16:40 #4
 Join Date
 Jun 2005
 Posts
 24
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sumif (2000)
Thanks Hans for ypur very rapid response. Unfortunately it didn't do what I wanted. I have probably done something wrong.
Please see the attachment. What I want is for the result that appears in cells G23 to V23, if the contents in cell in the same column at row 12 is included in the range E14:E25
So in the attachment the result in cell I23 should be 0 as DE4 is not included in the range E14:E25.
Hopefully this is more understandable.

20050613, 16:49 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Sumif (2000)
Does this do what you want?
=IF(NOT(ISNA(MATCH(I$12,$E$14:$E$25,0))),SUM(J$17: L$17)SUM(G$17:I$17),0)
or
=ISNUMBER(MATCH(I$12,$E$14:$E$25,0))*(SUM(J$17:L$1 7)SUM(G$17:I$17))

20050613, 16:57 #6
 Join Date
 Jun 2005
 Posts
 24
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sumif (2000)
Thats magic, thanks Hans, and Steve. Its exactly what I wanted, I can see that I will be calling on you guys again.
Could you just tell me what ISNA and ISNUMBER actually mean?
Thanks again.
Regards
Garry

20050613, 17:03 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Sumif (2000)
The ISNA function looks at its argument, and returns TRUE if it is (or results in) #N/A, and FALSE otherwise. (Note: N/A stands for Not Available)
The ISNUMBER function looks at its argument, and returns TRUE if it is (or results in) a valid number, and FALSE otherwise.
You can look up these functions in the builtin help for Excel, it provides the syntax and some examples.

20050613, 19:07 #8
 Join Date
 Jun 2005
 Posts
 24
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Sumif (2000)
Thanks Hans, you've been a great help.
Regards
Garry