1. ## 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.

2. ## 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.

3. ## 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

4. ## 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.

5. ## 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))

6. ## 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

7. ## 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 built-in help for Excel, it provides the syntax and some examples.

8. ## Re: Sumif (2000)

Thanks Hans, you've been a great help.

Regards
Garry

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•