SUMIF Formula (Excel 97)

Topic

New Reply

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.

Reply | Quote

Replies

How about:

=SUM(arg)-SUM(SUMIF(arg, “banana”),SUMIF(arg, “apple”),SUMIF(arg, “orange”))

Reply | Quote

Or ctrl/shift/enter:
=SUM(OFFSET(rg,,1)*(rg”Apple”)*(rg”Banana”)*(rg”Orange”))

Reply | Quote

Thanks I’ll try that.

Reply | Quote

Bob, can you explain your formula? I’m and too to figure it out. Thanks! –Sam

Reply | Quote

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?

Reply | Quote

That makes sense as long as I don’t think about it.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Gentlemen, etc.

Another tip, which I worked out by implementing a similar strategy. I have a table of values, as follows:

X Y X Y X Y
1 5 3 9 4 7

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:

{=SUM(IF(Header="X",A2:F2,0))/SUM(IF(Header="X",1,0))}

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:

{=3*STDEV(IF(Header="X",A2:F2,0))}

(“0” is folded into the calculation when the header is “Y”, the result is not accurate.)

{=3*STDEV(IF(Header="X",A2:F2,))}

( also gets folded into the calulation as zero.)

{=3*STDEV(IF(Header="X",A2:F2,NA()))}

(the final result is #N/A)

This one actually works:

{=3*STDEV(IF(Header="X",A2:F2,""))}

I think this works because “” is treated by the STDEV function (and other mathematical functions) as a non-numerical element, and is therefore ignored, instead of being treated as a numeric and included in the calculation as a zero. For that matter, the following formula also works, I believe for the same reason:

{=3*STDEV(IF(Header="X",A2:F2,"Nothing"))}

Reply | Quote

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

Reply | Quote

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 right-click 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;

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 re-editing the formula

Reply | Quote