Solved

Excel spreadsheet: Using a function in a sumif()

Posted on 2014-03-24
7
354 Views
Last Modified: 2014-03-24
I have two columns in a worksheet: Class and Amount

Class     Amount
HW     $200.56
SW      $303.32
HW     $111.11
IN       $222.22
XX       $56.56
YY        $543.22

I have subtotals (using sumif) for each valid product class:
Hardware would be $311.67
Software would be $303.32
Installation would be $222.22

I want to have a subtotal for "Miscellaneous" that adds up all non-valid Classes.

I already have a IsValidProductClass() function. Is there a way to use that in a SUMIF funtion to total all invalid product classes?
0
Comment
Question by:GeriR
7 Comments
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39951047
GeriR,

In order to work with custom function, you should upload a sample worksheet so that we can look at how the custom function works to determine a solution for you.
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39951077
You could add a column 'Valid class' and include only 'True' in the Sumifs.
Or you could have a separate sumifs using all  'do not equal' (ie: the opposite of current sumifs)

There's no way to call the function itself from within Sumifs and as Harry says without a view of the function no one can comment on how it might be changed.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39951143
You can also subtract all the valid sums from the grand total.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:GeriR
ID: 39951743
OK, I've attached a sample of what I am trying to do.

The workbook I'm working with is much more complicated.  

It is an order quoting tool that figures out all sorts of margins and profits and compares different types of pricing packages.

When my client is "finished" working on the quote, they want to be able to generate a worksheet for their customer showing only the quantity, prices, and product classes. They also want a subtotal box on the bottom. They want me to break out each of the valid product codes and then collect any invalid codes into a "Miscellaneous" box.

Unfortunately, their idea of "finished" is not quite final. They still want to be able to make minor tweaks.

So, I'm looking for a neat and simple way to create that box, and it has to still work if they then add or delete line items.
Sumif-Question.xlsm
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39951824
GeriR,

In you situation, I would say Saqib Husain, Syed's suggestion is the best.

Pretty much use the Grant Total subtracting all the rest.

=sum(b2:b19)-sum(B20:B25)
0
 
LVL 12

Accepted Solution

by:
Harry Lee earned 500 total points
ID: 39951939
GeriR,

If you insist of getting your custom function to work in your formula, have to change your custom function to the following to make it accept range instead of only 1 cell.

Function IsValidProductClass(sProductClass As Range) As Variant
Dim OutputArr As Variant
Dim I As Long, I2 As Long

I = sProductClass.Count
ReDim OutputArr(1 To I)
For I2 = 1 To I
    Select Case sProductClass(I2)
    Case Is = "HW"
        OutputArr(I2) = True
    Case Is = "SW"
        OutputArr(I2) = True
    Case Is = "IN"
        OutputArr(I2) = True
    Case Is = "ES"
        OutputArr(I2) = True
    Case Is = "HS"
        OutputArr(I2) = True
    Case Is = "SV"
        OutputArr(I2) = True
    Case Else
        OutputArr(I2) = False
    End Select
Next I2
IsValidProductClass = Application.Transpose(OutputArr)
End Function

Open in new window

Then, in your sumif cell, you have to enter array formula
=SUMPRODUCT(NOT(isvalidproductclass(A2:A18))*B2:B18)

Open in new window

by Ctrl-Alt-Enter instead of Enter normally.
0
 

Author Closing Comment

by:GeriR
ID: 39952103
Perfect - just what I was looking for. Thanks.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question