Comments on: SUMIF multiple columns with one or several criteria in Excel

Doing a conditional sum in Excel is a piece of cake as long as all the values to be totaled are in one column. Summing multiple columns is a problem because both the SUMIF and SUMIFS functions require the sum range and criteria ranges to be equally sized. Continue reading →

Comments page 2. Total comments: 93

  1. =(SUMIFS('STK Sheet'!H16:H400,'STK Sheet'!E16:E400,"#4 ACSR",'STK Sheet'!C16:C400,"N"))*(SUMIFS('STK Sheet'!D16:D400,'STK Sheet'!E16:E400,"#4 ACSR",'STK Sheet'!C16:C400,"N"))

    I have this formula which multiplies the linear footage of wire(column H) as long as column E shows a specific wire sizer and column C shows N for new wire. I have now created a column D that shows if there are 1, 2, or 3 wires at the location. the above formula does not work because it is taking the sum of the first statement and multiplying it by the sum of the 2nd. I need it to multiply each linear footage(H) by each number of wire(D) then sum all of that together.
    H1*D1 + H3*D3 + H7*D7 etc.
    any helpful tips?

      1. I tried this previously and still was unable to make it work the way I needed it to. If column E and C are true I need H and D to be multiplied and added respectively. Maybe I just was not writing my sumproduct correctly

        1. I believe I figured it out.
          =SUMPRODUCT(--('STK Sheet'!E16:E400="#4 ACSR"),--('STK Sheet'!C16:C400="N"),('STK Sheet'!H16:H400),('STK Sheet'!D16:D400))
          As I am sure you know the "--" makes those columns into numerical values so that it serves the same purpose as the SUMIF criteria

  2. may I ask how to add numbers from different column with #n/a (error) and text in cells and also hidden.
    example: How to add this numbers from A1 to H1 ignoring error and text and also hidden column or to add the numbers selected cells ignoring error and text. like A1+B1+E1+F1+H1=300 or A1:H1=300 but errors and text or hidden column are being ignored and the formula can be applied to the rest of the rows by dragging down or copying. thank you so much for your response to this.
    A1 B1 E1 F1 H1

    1. #N/A WAIVED 100 WAIVED 200
    2. 500 WAIVED 120 #N/A 50

  3. Using your example, I want the sum for Apples but only for January and March but then also removing the February.

    Is that possible using sumproduct, please?

    1. Please, anyone?

      1. Hi!
        Use data for January and March only

        =SUMPRODUCT((C2:C10) * (A2:A10=H1) * (B2:B10=H2)) + SUMPRODUCT((E2:E10) * (A2:A10=H1) * (B2:B10=H2))

        1. Hi is there a way to make this exact formula dynamic though? for example if i have 12 months of data, i only want to sum up the month columns for apple, if the date range is less than or equal to june. and then for grapes i might wnt to see date range is less than or equal to august. and so forth for other fruits. i want to be able to drop the formula without having to manually indicate with columns to sum up to.

          1. Hi! For the last example in the article above, you can add the month selection in cell H3. The column with the data of the desired month can be received with the INDEX function.

            =SUMPRODUCT((INDEX(C2:E10,,MATCH($H$3,$C$1:$E$1,0))) * (A2:A10=H1) * (B2:B10=H2))

        2. Thank you so very much!

          1. This can also be done with sumifs, right? Just trying to understand if the results would be indifferent in case we use sumifs for summing non -adjacent columns with single or multiple criteria

  4. Great work done by you! thanks alot for the details topics covered very briefly. Keep it up and shared the valuable knowledge with community.

  5. Greetings,

    Is there a way to use SUMIF, SUMIFS or SUM(IF) searching the criteria in an array?

    For example:

    =SUMIF(B1:B10,A1:A4,C1:C10)

    A B C D E F
    1 ABC ABC 10
    2 ABC BCD 25
    3 XYZ CDE 30
    4 DEF DEF 10
    5 RST 20
    6 DEF 10
    7 GHI 15
    8 TUV 20
    9 BCD 30
    10 CDE 10

    The result of the example would be = C1+C2+C4+C6+C9+C10

    My need for something like this is because in my spreadsheet my array of criteria have 70 values (for instance the 'B1:B4' in the above example is more like 'B1:B70'), and the sumrange have over 1000 lines.

    Thank you all in advance!

  6. Hi, I'd like to create a formula that multiplies data in pairs of cells if they're not blank:

    The idea is for a cost sheet where there are 6 columns.
    The choice is either hours and hourly cost OR number of days and daily rate OR an overall value which would just be multiplied by 1 for the total, then the total column at the end.
    I've tried sum/sum if (honestly been working my way around the self-help guides) but I'm clueless and at best I am only able to add 2 columns together whereas I want them multiplied for a total cost.

    Hours, Hourly Rate, Days, Daily Rate, Estimated Value, Total
    5, 5, blank, blank, blank, total = 5x5
    OR
    Blank, blank, 10, 10, blank, total =10x10
    OR
    Blank, blank, blank, blank, 500, total = 500x1 (or just to copy the value entered in the estimated value cell)

    Hope this is clear - it's the best I can explain without being able to add a screenshot or excel sheet.

    Thanks in advance for any help! :)

      1. That's worked great, thank you! :)

  7. Is there a chance to sum up the elements in column C in excel if the coordinates in columns A and B are repeated (at least once but maybe more)? So I mean a situation when it will be like this

    A B C
    0 1 4
    0 1 2
    0 1 3
    0 2 3
    ....

  8. =IF(COUNTIF(I5, "*mlg*"),H5/100000)COUNTIF(I5, "*grm*"),H5/1000))

    I am new in excel. Please solved this issue. What is right formula.

  9. Greeting to Ablebits team☺,

    I am thankful to you all for such amazing & detailed explanation on excel topics and "also for providing the excel sheet".

    But, In some of my previously read articles there are no excel sheets provided for practice.

    I would request the team to provide excel example sheet for all the articles. If possible, then please add excel example sheets of previous articles also.

    Feedback: -
    1) Kindly add Excel practice sheet for each article.
    2) Arrange all related topics sequentially.

  10. Hi,

    Anyone can help me what formula to use, basically I want to get the sum if date falls under Oct/22, Nov/22, Dec/22 and so on.
    Appreciate your help. Thanks!

    SKU 10/22/2022 10/29/2022 11/5/2022 11/12/2022 11/19/2022 11/26/2022 12/3/2022 12/10/2022 12/17/2022 12/24/2022 12/31/2022 1/7/2023 1/14/2023 1/21/2023 1/28/2023 2/4/2023 2/11/2023 2/18/2023 2/25/2023 3/4/2023 3/11/2023 3/18/2023
    40015048001 0 0 0 0 0 0 100000 0 0 0 200000 0 0 0 0 200000 0
    40015048002 0 0 50000 50000 50000 0 0 20000 20000 20000 50000 50000 50000 0 0 0 50000
    40015048003 47300 40000 120000 60000 15000 0 0 0 0 0 19084 19084 19084 59358 59358 59358 59358
    40015048004 78720 0 0 0 0 0 0 1324 0 0 19084 19084 19084 59358 59358 59358 59358

    1. Hello!
      If dates are written in the first column, I recommend reading this guide: How to use Excel SUMIF with dates. This should solve your task.
      If the dates are written in the first row, try the SUMPRODUCT formula:

      =SUMPRODUCT((B1:R1=DATEVALUE("22.10.2022"))*B2:R5)

      I hope it’ll be helpful. If this is not what you wanted, please describe the problem in more detail.

  11. The formula =SUM((C2:E10)*(--(A2:A10=H1))) doesn't work with me neither by pressing ENTER or CTRL+SHIFT+ENTER.

  12. Hi
    How can I sum something like this:

    WA11
    100
    250

    WA9
    3000

    WA11
    25
    333

    Just sum of all WA11, and they are all in same column?

    Thanks

    1. Hi!
      To calculate the sum by condition, you need an additional column for this condition. Add column B. In cell B1, write
      =A1.
      In cell B2:
      =IF(ISTEXT(A1),A1,IF(ISNUMBER(A2),B1,""))
      Copy this formula down along the column. Then apply the SUMIF formula.

      1. Thank you for your help.

        Is possible to separate text only if they begin with asterisk * ?

  13. Feel a bit greedy to give away my personal findings, but "the time has come". Cool kids don't use SUMIF (no offence) while there is more elegant and practical way to aggregate data with the arrays. You described the idea very well, my comment is just for the execution. SUM(IF(A2:A10=H1, C2:E10)) and ctrl + shft+enter gives the same result but in a more intuitive way. And this logic is applicable to any aggregate function such as avg, median or count. Moreover, number of conditions has no limits as well. Lets say we want apples for Jan and Feb: SUM(IF(A2:A10=H1,IF(C1:E1 "Mar", C2:E10))) ctrl + shft+enter. This logic can be also implemented inside index - match, but that's another story...

  14. Using the table above, how would I create the formula to find the total sales of Apples in the East during March, without creating values in the H column?

    Thank you so much!
    James

  15. I learned new thing to solve my problem today.
    I am trying to sum values in an array if the condition is met. This array width is ever changing based on criteria.
    I leave the setting of array width to later part as I did not succeed at the fixed width array too.

    My data is set as Table in excel instead of range so that all formula grow together with the table when user add new data.
    Here is my formula:
    =SUMPRODUCT((RegByModel[[#All],[Jan-15]:[Mar-15]])*(--(RegByModel[[#All],[Model Name]]=Calculation!G81)))

    It should sum all values in Table "RegByModel", column [Jan-15] to [Mar-15],
    if the criteria in Table "RegByModel", column [Model Name] equals to "Calculation!G81" which is a text value.

    If I break the formula into 2 parts, it works fine. But when I combine them into SUMPRODUCT formula, it gave #VALUE! error.
    I tried using SUM formula too, same condition.

    I use the function "Evaluate Formula" to check, it returns that the first part of the formula is causing error.
    The first part of the formula is (RegByModel[[#All],[Jan-15]:[Mar-15]])

    If anyone might have an idea on how to solve this, appreciate your input.
    Thanks!

      1. Thanks for your quick reply Alexander!
        Can I try to understand more which size is not same? Cell height or Width?

        If I compare to the example =SUMPRODUCT((C2:E10) * (A2:A10=H1))

        (C2:E10) = RegByModel[[#All],[Jan-15]:[Mar-15]] >> There is 3 columns in the example, and 3 columns in my formula. Each column denotes a month.
        A2:A10 = RegByModel[[#All],[Model Name]] >> Single column for example and my formula.
        H1 = Calculation!G81

        Hope to get your further reply. Thanks.

          1. Thanks Alexander for your kind help.
            Think I need to break this intention into 2 steps.

  16. This is very insightful. Pivot tables can also be used to get the required values. Though we have to update it if anything changes in the data set....Nice analysis, especially with the sumproduct and sum functions.

  17. Please can the SUMIF with conditions return a blank cell?

    1. Hi!
      The SUMIF function returns a number. If nothing is found, it returns 0. You can replace zero with an empty value using the IF function.
      For example,

      =IF(SUMIF(B2:B10, F1, C2:C10) > 0,SUMIF(B2:B10, F1, C2:C10),"")

  18. The writer of this article is the Hero we didn't know we needed.... Until we needed it.

    I had a hell of a time Getting the Proper SUMIFS format after i needed to add a second Sum range.

    Worked like a champ

    =SUMIFS('Monthly Expenses'!D4:D27,'Monthly Expenses'!C4:C27,Values!E1) +SUMIFS('Monthly Savings'!C4:C10,'Monthly Savings'!B4:B10,Values!E1)

    Thanks

  19. Thank you, this article is my lifesaver. I spent hours and days trying a find a solution before stumbling upon this. :)

  20. Very nice; very elegant. Thank you!
    It's been years since I used Excel, and then only as an amateur.

    Just wanted to point out that a possibly-hidden row (above or below) could have cells that each contain a simple SUMIF for their particular column. The grand total would simply sum horizontally across those cells.

    I used something like this when I wanted cyclic period information that would expire after 'n' periods. Insert a new column between the titles and the previous period data, and you're off and running.

    I've bookmarked this blog for future reference.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)