+ Reply to Thread
Results 1 to 4 of 4

sumif but only if category contains values

  1. #1
    Forum Contributor
    Join Date
    05-12-2021
    Location
    USA
    MS-Off Ver
    Microsoft Office LTSC Professional Plus 2021
    Posts
    212

    sumif but only if category contains values

    Hi I'm looking for some help on a sumif formula in the attached file. I have 4 different categories(1 thru 4) of schedules that I want this formula to add together. A lot of different schedules are listed in column A with their corresponding Category number in B. In Column M, a total of needed number of people on a particular schedule is manually entered. What I want just as one example in O5 is if the sum of all category 1's entered totals in column M = 0, to just leave the cell blank. But if they do total a value, I want it to add up all the numbers of category 1 and divide that total by 100. I wasn't sure what type of formula to use in combination with a sumif formula to do this. Thank you for your time.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    12-28-2010
    Location
    London
    MS-Off Ver
    Excel 365
    Posts
    72

    Re: sumif but only if category contains values

    If i have understood correctly, i would do 2 things
    Change the format of O5 to

    0.0%;0.0%;0

    (this displays zeros as 0 and not as a percentage)

    and use the formula

    =IF(SUMIFS(M5:M31,B5:B31,1)=0,0,SUMIFS(M5:M31,B5:B31,1)/100)

    which first checks if the calculation is zero and if so displays 0, else runs the calc

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,215

    Re: sumif but only if category contains values

    This ??


    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  4. #4
    Forum Contributor
    Join Date
    05-12-2021
    Location
    USA
    MS-Off Ver
    Microsoft Office LTSC Professional Plus 2021
    Posts
    212

    Re: sumif but only if category contains values

    Thank you. I must have gotten the formula confused then. I thought that you couldn't use an IF formula with a sumif type formula. I'm thinking there is one formula type that you can't use it with but it escapes me at the moment. Thank you for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] SumIF by Category and Date Range.
    By trini27 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-20-2021, 06:03 AM
  2. Sumif more than 1 category on 1 column, (NOT SUMIFS)
    By pvckmyers in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 03-04-2020, 11:00 PM
  3. Sumif and Hlookup - by Month by asset category
    By simplydab in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-20-2019, 03:12 PM
  4. SUMIF Adding up names in a category
    By priestyuk in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-24-2018, 09:31 AM
  5. [SOLVED] SUMIF for category sales
    By makinmomb in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-02-2016, 10:58 AM
  6. How to Chart values for Division vs Category and Sub-Category?
    By rahulgk in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-11-2013, 06:33 AM
  7. Automatic Sum by Category (SumIf extension)
    By ezuk in forum Excel General
    Replies: 1
    Last Post: 08-12-2005, 02:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1