Results 1 to 12 of 12
Thread: SUMIF Formula (Excel 97)

20011030, 20:22 #1
 Join Date
 Sep 2001
 Posts
 32
 Thanks
 0
 Thanked 0 Times in 0 Posts
SUMIF Formula (Excel 97)
Hello,
Can you write a SUMIF statement so that it will be able to Sum only if the condition is not met? Almost like a SUMIFNOT type of formula?
Example:
Apple 10
Beer 24
Banana 10
Orange 10
Rum 10
The formula will read the data and sum only if it is not Apple, Banana, or Orange. Therefore it will total 34.

20011030, 21:04 #2
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: SUMIF Formula (Excel 97)
How about:
=SUM(arg)SUM(SUMIF(arg, "banana"),SUMIF(arg, "apple"),SUMIF(arg, "orange"))John ... I float in liquid gardens
UTC 7ąDS

20011031, 13:30 #3
 Join Date
 Jun 2001
 Posts
 91
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUMIF Formula (Excel 97)
Or ctrl/shift/enter:
=SUM(OFFSET(rg,,1)*(rg<>"Apple")*(rg<>"Banana")*(r g<>"Orange"))

20011031, 13:32 #4
 Join Date
 Sep 2001
 Posts
 32
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUMIF Formula (Excel 97)
Thanks I'll try that.

20011031, 14:16 #5
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: SUMIF Formula (Excel 97)
Bob, can you explain your formula? I'm <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> and too <img src=/S/snore.gif border=0 alt=snore width=32 height=15> to figure it out. Thanks! Sam
<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

20011031, 14:32 #6
 Join Date
 Jun 2001
 Posts
 91
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUMIF Formula (Excel 97)
Sure! Assume rg is defined as A1:A5 and contains Apple, Pear,Banana,Orange,Grape and B1"B5 contains 10,20,30,40,50.
An expression like rg<>"Apple" would compare each item of rg against the string "Apple" and return something like {FALSE;TRUE;TRUE;TRUE;TRUE} where TRUE means that item of rg is NOT = "Apple"
TRUE * TRUE is 1, all other combinations produce 0.
So, multiplying these rg<>"whatever" is something like
{FALSE;TRUE;TRUE;TRUE;TRUE} * {TRUE;FALSE;TRUE;TRUE;TRUE} which is {0;0;1;1;1}.
Offset(Rg,,1) is the column of #s next to rg, or the 10,20,30,40,50. Multiplying it all out is like:
{10;20;30;40;50}*(0;1;1;1;1}*{1;1;0;1;1}*{1;1;1;0; 1} which is {0;20;0;0;50}.
This is then passed into the SUM, for a result of 70.
Capisce?

20011031, 15:18 #7
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: SUMIF Formula (Excel 97)
That makes sense as long as I don't think about it. <img src=/S/salute.gif border=0 alt=salute width=15 height=20>
<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

20011102, 19:51 #8
 Join Date
 Nov 2001
 Location
 MI, USA
 Posts
 33
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUMIF Formula (Excel 97)
The other tips are great...
but for real world applications, I think I would use a 3rd column (Col A1:A5 in my below example) for *Category* . In your example, you wanted the sum of booze
A) I would *sumif* for booze in A6  the advantage here is that if your list of categories include veggies, booze, soft drinks etc then in A6, you can use Excel's *Pick From List...* function to automatically fill in that so called criteria selector cell.
EXAMPLE: In one of my shop spreadsheets, i kept a list of over 150 automotive parts ordered over a period of time amounting to about 2,000 rows for a particular vehicle model. In the last row, I placed the sumif statements in the QTY, Price and Status columns and referenced their criterias to the selector cell directly under the part nomenclature column. I also tied the part number using VLOOKUP to the part nomenclature selector. Anytime I wanted to know about part X or whichever, I would rightclick on the part name cell and click on PICK FROM LIST..., scroll down and click on the part I wanted to know about. The results would give me the qty ordered, total price and how many were filled and is great from a QA perspective for identifying parts and part numbers that constantly need replacing;
[img]/forums/images/smilies/cool.gif[/img] In the event you want to mix it up as to what gets counted or not, then I would solve for *X*, where x marks the item I want counted *=SUMIF(A1:C5,A6,C1:C5)*. In column A, I would enter x in every cell that I want counted and also enter x in A6 (my criteria cell).
Either case, I can solve for various scenarios on the fly by changing the criteria selector cell of the category column w/o reediting the formula

20011217, 02:15 #9
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,921
 Thanks
 6
 Thanked 9 Times in 7 Posts
Re: SUMIF Formula (Excel 97)
Bob,
I've been playing with array formulas, as you know.
So for this, I came up with
{=SUM(ISNA(MATCH(items,exclude_list,0))*values)}
where
items is a range containing the original list of fruits, drinks
values is a range to be summed
exclude_list is a list of items to be excluded from the sum
With your tutorial, I think I'm getting the hang of these things now.
Fred

20011227, 14:44 #10
 Join Date
 Dec 2001
 Location
 Netherlands
 Posts
 7
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUMIF Formula (Excel 97)
Hi Fred could you give me an Example sheet because I am having some difficulties.
For the Items I can only select single cells no ranges as well as for the values ?
Many thanks because the formule could be very very helpfull for me to automate a lott of stuff.
Robert.

20011227, 18:43 #11
 Join Date
 Mar 2001
 Location
 Silicon Valley, California, USA
 Posts
 273
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: SUMIF Formula (Excel 97)
Gentlemen, etc.
Another tip, which I worked out by implementing a similar strategy. I have a table of values, as follows:
<pre>X Y X Y X Y
1 5 3 9 4 7
</pre>
I need to calculate average, 3 sigma, range, and range/average in the following three ways: All, X only, Y only.
For average ( X only shown below), the following array formula works great:
<pre>{=SUM(IF(Header="X",A2:F2,0))/SUM(IF(Header="X",1,0))}
</pre>
It works because a "0" does not affect the resulting SUM's. However, for the other calculations, a "0" DOES adversely affect the calculation. For example, the following array formulas do not work for 3 sigma:
<pre>{=3*STDEV(IF(Header="X",A2:F2,0))}
</pre>
("0" is folded into the calculation when the header is "Y", the result is not accurate.)
<pre>{=3*STDEV(IF(Header="X",A2:F2,))}
</pre>
(<nothing> also gets folded into the calulation as zero.)
<pre>{=3*STDEV(IF(Header="X",A2:F2,NA()))}
</pre>
(the final result is #N/A)
This one actually works:
<pre>{=3*STDEV(IF(Header="X",A2:F2,""))}
</pre>
I think this works because "" is treated by the STDEV function (and other mathematical functions) as a nonnumerical element, and is therefore ignored, instead of being treated as a numeric <nothing> and included in the calculation as a zero. For that matter, the following formula also works, I believe for the same reason:
<pre>{=3*STDEV(IF(Header="X",A2:F2,"Nothing"))}
</pre>

20011227, 21:55 #12
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,921
 Thanks
 6
 Thanked 9 Times in 7 Posts
Re: SUMIF Formula (Excel 97)
Robert,
Attached is a sample sheet (not with the exact same items but the intent is the same) which shows both my array formula as well as a "normal" SUMIF approach to summing items not on an exclusion list. This was what the original problem was. As you can see, it's not clear that there's much difference to one approach vs the other. I'd be curious as to why you think the array formula approach can help you more than the SUMIF approach.
Fred