SUMIFS in Google Sheets to sum cells with multiple criteria (AND / OR logic)

The tutorial shows how to use the SUMIFS function in Google Spreadsheets to sum cells with multiple criteria. You will find a handful of real-life SUMIFS formula examples with AND as wells as OR criteria.

Google Spreadsheets provide two functions to conditionally sum cells, SUMIF and SUMIFS. Regular visitors of our blog are already familiar with SUMIF that we covered last week, and today we are going to have a closer look at its plural version.

The difference between SUMIF and SUMIFS is as follows:

SUMIF can add up cells based on just one condition. If it is what you want, please check out this tutorial: SUMIF in Google Sheets.

SUMIFS can sum cells based on two or more conditions. Further on in this tutorial, you fill find a simple explanation of SUMIFS syntax and formula examples for different data types.

Google Sheets SUMIFS function - syntax & basic uses

The SUMIFS function is Google Sheets is designed to sum numbers with multiple conditions. Its arguments are as follows:

SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, …])

Where:

  • Sum_range(required) - the range to be summed.
  • Criteria_range1 (required) - the range of cells to be evaluated by criterion1.
  • Criterion1 (required) - the condition to be met by cells in criteria_range1.
  • Criteria_range2, criterion2, … (optional) - additional ranges and criteria to test.

SUMIFS in Google Sheets - 3 things to remember

Using SUMIFS formulas in Google spreadsheets is quite easy. To prevent most common errors, just remember these three simple facts:

  1. The SUMIFS function adds up only those cells that meet all conditions, i.e. all of the specified criteria are true for a cell. This is commonly referred to as AND logic.
  2. Sum range and all criteria ranges should be equally sized, i.e. have the same number of rows and columns, otherwise your SUMIFS formula will throw a #VALUE error.
  3. Depending on the nature of your conditions, build your criteria appropriately:
    • Text, wildcard characters and dates should be enclosed in quotation marks like "apples" or "*" or "4/19/2024".
    • Numbers and cell references are not enclosed in quotation marks when used on their own.
    • Logical operators with numbers, dates or text must be enclosed in double quotes like ">200", ">4/19/2024" or "<>apples" .
    • Logical operators used together with cell references and other functions require the quotes to begin a text string and ampersand to concatenate and finish the string off. For example: ">"&B1 or ">="&TODAY().

Okay, that's enough theory, let's build some useful formulas already!

How to sum with multiple criteria (AND logic)

When it comes to summing cells with multiple criteria, you may find a lot of different suggestions in Google groups and other user communities.

Let's say, you have a list of items in column A, amounts in column B, and delivery status in column C. Your goal is to get a total for a specific item with a certain delivery status, for example "apples" that are "delivered". How would you approach the task?

You may be surprised to know that many people still use array formulas for this purpose. For example, this one:

=ARRAYFORMULA(SUMPRODUCT((A2:A13="apples")*(C2:C13="delivered")*(B2:B13)))

What's wrong with this formula, you may ask me. Nothing except that it's excessively complex, difficult to understand and debug. It's like using a sledgehammer to crack a nut. Google Sheets SUMIFS function provides an easier way!

So, let's go ahead and define our arguments:

  • Column A should contain "apples". Assuming we have data in rows 2 to 13, our criteria_range1 is A2:A13, and criterion1 is "apples".
  • Column C should contain "delivered". Meaning, our criteria_range2 is C2:C13, and criterion2 is "delivered".
  • Numbers to sum are in column B, so our sum_range is B2:B13.

Putting the arguments together, we get this simple formula:

=SUMIFS(B2:B13, A2:A13, "apples", C2:C13, "delivered")

To add more flexibility, you can input both criteria in separate cells, say F1 and F2, and refer to those cells:

=SUMIFS(B2:B13, A2:A13, F1, C2:C13, F2)

As shown in the screenshot below, our SUMIFS formula works perfectly! SUMIFS in Google Sheets to sum cells with multiple criteria

Taking the example further, let's see how to use SUMIFS with other criteria types.

SUMIFS with logical operators

When your conditions are numbers or dates, use the comparison operators to express the criteria:

  • equal to (= or omitted)
  • not equal to (<>)
  • greater than (>)
  • less than (<)
  • greater than or equal to (>=)
  • less than or equal to (<=)

For example, to add up amounts in B2:B13 that are greater than or equal to 200 and delivered before 1-Apr-2024, use this formula:

=SUMIFS(B2:B13,B2:B13,">=200", C2:C13, "<4/1/2024")

In case you want to replace actual values with references to cells holding your criteria, please remember to enclose the logical operator in quotation marks and to concatenate the cell reference by using an ampersand:

=SUMIFS(B2:B13, B2:B13, ">="&F1, C2:C13, "<"&F2)

The screenshot below shows the result: SUMIFS formulas with logical operators

SUMIFS with other functions

In some situations, the conditions may depend on the results returned by some other functions. In this case, embed those functions in the criterion arguments of your SUMIFS formula.

As an example, let's sum "apples" that are "delivered". In other words, the delivery date is up to and including today, so we concatenate the "less than or equal to" operator (<=) with the TODAY() function:

=SUMIFS(B2:B13,A2:A13,F2,C2:C13,"<="&TODAY())

And get the following result: SUMIFS with other functions as criteria

SUMIFS with blank and non-blank cells

To sum numbers in one column depending on whether a cell in another column is empty or not empty, use one of the following criteria:

  • "=" to sum cells that are completely blank, i.e. contain absolutely nothing.
  • "" to sum empty cells including zero length strings.
  • "<>" to sum non-blank cells including zero length strings.

In case the Delivery date column contains some gaps, you may want to sum only those "apples" for which the delivery date is set, i.e. a cell in column C is not empty. The task can be accomplished with this formula:

=SUMIFS(B2:B13,A2:A13,"apples",C2:C13,"<>") SUMIFS to exclude empty cells

How to sum with multiple criteria (OR logic)

As you already know, by default, Google Sheets SUMIFS function works with AND logic - all conditions must match to be summed. In some situations, however, you may need to conditionally sum cells with OR logic, when any of the specified criteria is true. Below you will find a few possible ways to do this.

SUMIF with OR criterion (multiple criteria in the same column)

To sum cells with only a couple of criteria in the same column, you can simply add two SUMIF functions together.

For example, to sum the amounts in column B if column A is either "apples" or "bananas", use the SUMIF() + SUMIF() combination in its simplest form:

=SUMIF(A:A,"apples",B:B) + SUMIF(A:A,"bananas",B:B)

If you have three or more criteria, you may be looking for a more compact formula. For this, include the items in an array constant (aka inline array), use ArrayFormula to get a subtotal for each item, and wrap the whole construction into SUM() to add the subtotals together. For example:

=SUM(ARRAYFORMULA(SUMIF(A2:A13, {"apples", "bananas", "lemons"}, B2:B13)))

Instead of "hard-coding" the items in an array constant, you can enter them in individual cells, and include cell references in the array (in case of non-contiguous cells) or supply a range (in case of contiguous cells).

To better show you that our SUMIF formula works in exact accordance with the specified OR criteria, I've narrowed down the list to two items:

=SUM(ARRAYFORMULA(SUMIF(A2:A13, {E1, E2}, B2:B13)))

Or

=SUM(ARRAYFORMULA(SUMIF(A2:A13, E1:E2, B2:B13))) Sum cells with multiple OR criteria and output a total into one cell.

Instead of ArrayFormula, you can add up subtotals with the SUMPRODUCT function. For a short list of items, you can put it this way:

=SUMPRODUCT((A2:A13="apples") + (A2:A13="bananas"), B2:B13)

For multiple OR criteria (three or more items), you'd better use this syntax:

=SUMPRODUCT((A2:A13={"apples", "bananas"}) * B2:B13)

Replace the array elements with cells references, and you will get the most compact formula to sum cells with multiple OR criteria ever!

=SUMPRODUCT((A2:A13={E1, E2}) * B2:B13)

The screenshot below shows the result: A SUMPRODUCT formula to sum cells with OR criteria

Four different formulas, the same result. Which one to use is the matter of your personal preference :)

SUMIF with OR criterion and results in different cells

If you'd like to have a subtotal for each item in a separate cell, take an array SUMIF formula discussed above, adjust the references, and cut off the SUM() part:

=ARRAYFORMULA(SUMIF(A2:A13, {"apples", "bananas", "lemons"}, B2:B13))

This will give you a sum for each item in a separate cell as shown in the screenshot below: Sum with OR criteria and get sub-totals in separate cells.

For the sake of clarity, you enter the formula only in the leftmost cell (D2 in this example), and Google Sheets will put the results into as many cells as many items there are in your array constant. Just make sure you have enough empty cells to the right, otherwise, the result won't be expanded and the formula will turn into an error.

If you'd rather have subtotals in a column, then separate the array elements with semicolons to make a vertical array:

=ARRAYFORMULA(SUMIF(A2:A13, {"apples"; "bananas"; "lemons"}, B2:B13))

And the results will be output vertically in a column: Sum with OR criteria and output sub-totals in a column.

Tip. As usual, you can make your formula more flexible by replacing a hard-coded array with a range reference. That way, your users could type any items in the predefined cells, and you won't have to worry about updating your formula. For example, the formula shown in the screenshot above, can take the following shape:
=ARRAYFORMULA(SUMIF(A2:A13, D1:D3, B2:B13))

SUMIFS with OR criteria (multiple criteria in different columns)

This example shows how to sum numbers with several sets of conditions determined by the following logic.

  • Within each set, all conditions must be true (AND logic)
  • A cell is summed if any set of conditions is true (OR logic)

To make things easier to understand, please consider the following example. In our sample data set, supposing you want to sum amounts in column B if column A contains either "apples" OR "oranges" AND the delivery date in column C is "16-Mar-2018".

The most obvious way is to make two SUMIFS formulas to sum "apples" and "oranges" separately, and then add up the results:

=SUMIFS(B2:B13, A2:A13, "apples", C2:C13, "16-Mar-2024") +
SUMIFS(B2:B13, A2:A13, "oranges", C2:C13, "16-Mar-2024")

Or, you can enter your criteria in some cells, as shown in the screenshot below: SUMIFS with OR criteria

Regrettably, Google Sheets do not allow expressing multiple OR conditions using array constants, therefore we cannot make a plural version of our SUMIF with OR criteria formula. Luckily, there is another way to achieve the same result with a bit shorter formula - the SUPRODUCT function.

As an example, let's enter the desired delivery date (AND criterion) in F2 and items (OR criteria) in contiguous cells, say F1 and G1. Now, you can use the following formula to sum numbers in B2:B13 based on the above criteria:

=(SUMPRODUCT(--(C2:C13=F2), (--(ISNUMBER(MATCH(A2:A13, F1:G1, 0)))), B2:B13)) A SUPRODUCT formula to sum cells with multiple OR criteria

How this formula works

Working from the inside out, here's what you do:

  • Check if an item is "apple" or "orange":
    ISNUMBER(MATCH(A2:A13, F1:G1,0))

    As the result, you'll get an array or TRUE and FALSE values (TRUE if any of the specified criteria is met, FALSE if none of the criteria is met).

  • Compare a list of dates with the target date: C2:C13=F2

    Instead of a cell reference, you can enter the date directly in a formula by using DATEVALUE or DATE function. For example, C2:C13=DATEVALUE("3/16/2024") or C2:C13=DATE(2024,3,16)

    In this formula, however, you cannot compare a range with a date directly, like C2:C13="3/16/2024" or C2:C13="16-Mar-2024", because you'd be comparing a cell with a date to a text string. Google Sheets won't understand that and you will most likely get a zero result.

    If done correctly, this comparison will give you another array or TRUE and FALSE.

  • Use the double unary operator (--) to coerce both arrays of TRUE and FALSE to 1 and 0, respectively.
  • Supply the range to sum: B2:B13

SUMPRODUCT will first multiply the elements of 3 arrays (two arrays of 0's and 1's and an array of numbers in B2:B13), and then sum the products. Because multiplying by 0 always yields 0, only the cells that have 1 in the first two arrays will "survive". As the result, only the amounts with the specified delivery date and item names will be summed.

This is how you use SUMIFS in Google Sheets to sum cells with multiple conditions. To have a closer look at the formulas discussed in this tutorial, I invite you to open our SUMIFS Google Sheets Examples. I thank you for reading and hope to see you on our blog next week!

Spreadsheet with formula examples

SUMIFS Google Sheets Examples (make yourself a copy)

Table of contents

181 comments

  1. How do I use SUMIF to only a select and take the sum of the two smallest/largest/middle values?

  2. Hi I would like to combine these 2 formulas into 1 using SUMIFS or whatever it is I need but I am having a hard time. They both work correctly individually.

    The first formula below looks through a column of hours and sums them only if they fall within range of a month. For example, it will sum only if hours were entered in January 2022. (A1 is the date for example 1/1/2022)

    =sumifs(G7:G805,H7:H805,">="&DATE(YEAR(A1),MONTH(A1),1),H7:H805,"<="&EOMONTH(A1,0))

    The 2nd formula sorts through a column and sums them only if a "SUBJECT TAUGHT" matches. (In my example it sums up only if Pathology was lectured.

    SumIF(I7:I37,"Pathology",G7:G37)

    Basically I am trying to nest or combine them so that it sums up the subject taught only if it falls within a month/date range.

    • Hi Danny,

      If I understand you correctly, you just need to add the criteria_range & the criterion from the second formula into the first one:
      =sumifs(G7:G805,H7:H805,">="&DATE(YEAR(A1),MONTH(A1),1),H7:H805,"<="&EOMONTH(A1,0),I7:I805,"Pathology")

      However SUMIFS requires all arguments to be of the equal size, so you'll have to extend I7:I37 to I7:I805.

      If this doesn't work for you, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get (the result sheet is of great importance and often gives a better understanding than any text description). I kindly ask you to shorten the tables to 10-20 rows.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Once you share the file, just confirm by replying to this comment.

      I'll look into it.

      • Hi, I'm having a similar issue. I have two sheets within a document: Sheet 1, "Pedidos" with a list of transactions showing date, quantity and price in the columns; Sheet 2, "Sumario Mensal" with a summary of products sold within the month for all of 2022.

        When I use SUMIFS to calculate quantity "quantidade" sold within a date range, it returns the correct value: =SUMIFS(Pedidos!$C$3:$C,Pedidos!$A$3:$A,">="&F$1,Pedidos!$A$3:$A,"="&F$1,Pedidos!$A$3:$A,"<="&EOMONTH(F$1,0),Pedidos!$B$3:$B,"="&B3)

        However, when I try to combine both formulas Sheets doesn't recognize the second formula as criteria_range 3, criterion. It keeps reading it as criteria_range 2. Have you ever seen anything like this?

        I'll share the Sheet with the email you mentioned and if you guys can help me, it would be awesome!

  3. Hi Natalia,

    i would like to have a formula to SUM a range in column D with criteria in column F (with text "x" or any number), the SUM are from row D2:D6.
    i was try with this formula LOOKUP(1;1/(F2:F10);F2:F10) to find the last filled cell but i can not found how to combine with the SUM formula just to SUM from D2:D6 with said criteria.
    with the criteria i do expect to able to calculate or SUM until any criteria as filled cell in column F. (text or any number)

    A B C D E F
    1 BALANCE PROFIT PROP+BAL WD LAST BALANCE TO SUM
    2 100,00 13,00 113,00 1,30 111,70
    3 111,70 14,52 126,22 1,45 124,77
    4 124,77 16,22 140,99 1,62 139,37
    5 139,37 18,12 157,48 1,81 155,67
    6 155,67 20,24 175,91 2,02 173,89 x
    7 173,89 22,61 196,49 2,26 194,23
    8 194,23 25,25 219,48 2,53 216,96
    9 216,96 28,20 245,16 2,82 242,34
    10 242,34 31,50 273,84 3,15 270,69

    WD up to "x" : 8,21

    i do appreciate i you can share me the formula and many thanks for your kind support.

    salam,
    agus.

      • Wow....
        this is really amazing formula, first i put as a normal formula and the result is ERROR with error message #VALUE!.
        i was try to execute every single formula on those but still error then i realize that this is an array formula so i press CTRL-SHIFT-ENTER when executing the formula and it is working fine.

        really really appreciate for quick response and thanks a bunch for the shared formula.

        salam,
        agus.

        • Agus,

          I'm really sorry, I did forget to mention that it's an array formula! Glad to know you figured it out though – hope it didn't take too long :)

  4. Hi, I am at my wits end. I have been trying to use countifs and sumifs to generate results in a table with no luck. Long story short, My data sheet is A1:K150, Column G has a dollar value (Dollar Amount), Column G, text/number combo (reason code) and Column J is text (Action Taken)
    Example 1 that I could get Reason code is R13, I needed to count the times Reason code R13 was taken as a loss
    Formula used with success is =COUNTIFS($H$3:$H$150,"R13",$J$3:$J$150, "Loss")

    Now what I am trying to do is take the sum of the amount in column g and include it to the same results. Rather than count how many, I would like to sum to total of Column G where Column H is matches the reason code and column J is the action taken. IE Sum of items for R13 taken as loss.

    Any help would be greatly appreciated.

  5. So I did a Sumifs function to add up day pay within the date range of the pay period...easy stuff because it's a basic sum.

    What I would love to do (but can't quite figure it out) if write something similar but to subtract (clock in and clock out times) also within a date range.

    I'm just savy enough to be dangerous...am I wasting my time trying to figure this out? Is it even possible to find a difference in two cells with date range criteria?

    • Hello Brenda,

      For me to be able to help you, please create a sample spreadsheet with 2 sheets: (1) a sample of your data (2) the result you expect to get; and share it with us – support@apps4gs.com. The result sheet is of great importance and often gives us a better understanding than any text description. I kindly ask you to shorten the tables to 10-20 rows. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      I'll look into your task and do my best to help.

  6. Hi! I have a very complex formula (for me) that works in excel, but not Sheets. Can anyone show how to convert this for Sheets? It appears that I need the OR logic for the value arrays in SUMIFS and COUNTIFS, but my childish attempts to solve this have failed.

    The article gives two options for OR logic using the non-S version, but it seems I still need to retain the S version of these functions. Any help is appreciated.

    =IFERROR(SUM(SUMIFS(Sets!H:H,Sets!E:E,A9,Sets!C:C,{235,364,405,412,416,423,469},Sets!J:J,">="&OSS!$B$3,Sets!G:G,">"&$B$4,Sets!F:F,">="&$B$5))/SUM(COUNTIFS(Sets!E:E,A9,Sets!C:C,{235,364,405,412,416,423,469},Sets!J:J,">="&OSS!$B$3,Sets!G:G,">"&$B$4,Sets!F:F,">="&$B$5)),"")

    • I solved my issue using the formula below. Regexmatch is needed for SUMIFS I guess. Along with some shuffling of the functions.

      =IFERROR(ArrayFormula(SUMIFS(Sets!H:H,Sets!E:E,A9,REGEXMATCH(Sets!C:C,"^(235|364|405|412|416|423|469)$"),TRUE,Sets!J:J,">="&OSS!$B$3,Sets!G:G,">"&$B$4,Sets!F:F,">="&$B$5))
      /SUM(ARRAYFORMULA(COUNTIFS(Sets!E:E,A9,Sets!C:C,{235,364,405,412,416,423,469},Sets!J:J,">="&OSS!$B$3,Sets!G:G,">"&$B$4,Sets!F:F,">="&$B$5))),"")

  7. Hello, In the spreadsheet below, on the first sheet called "Payouts", I am currently using Sumif in column B to total up values in column H from the second sheet called "Worksheet" if the name in the drop down column A matches any of the names in "Worksheets" column A.

    I want to add a criteria that the Checkbox in column D in "Worksheet" be checked (=True) to include that row in the sum.

    https://docs.google.com/spreadsheets/d/1qowl-flGEafWZJCvlf07GCrOWqsfdo68w8XA_S8Uad0/edit?usp=sharing

  8. Hello,
    from AL to BO i have scores (they range from 1 to 3) and from BS to CV i have the matching values either a or b or c. I would like to match total scores for a, b, or c for each row (one individual who completes a test).
    I have tried many formulas that at best gives me a 0 or an error.
    for example:
    =SUMIFS(BS3:CV3;"a";AL3:BO3) gives a 0
    =SUMIFS(AL2:AO2,BS2:BT2,"a") gives an #ERROR

    am I trying something impossible to solve or could you provide me with some hints?
    Thanks in advance for your time.

    • Hello Giulia,

      For me to be able to help you, please create a sample spreadsheet with 2 sheets: (1) a sample of your data (2) the result you expect to get; and share it with us – support@apps4gs.com. The result sheet is of great importance and often gives us a better understanding than any text description. I kindly ask you to shorten the tables to 10-20 rows. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      I'll look into your task and do my best to help.

  9. Hey, how can I make this work for even or odd rows? Here’s what I’ve come up so far, but it says the argument must be a range:

    =SUMIF(ISEVEN(ROW(C62:C76)),""&"",ISEVEN(ROW(A62:A76)))

    • Hey Dyn,

      To sum numbers from the even rows, try a formula like this:
      =SUM(FILTER(C62:C76,ISEVEN(ROW(C62:C76))))

      If this doesn't work as you expect, for me to be able to help you, please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your data (2) the result you expect to get (the result sheet is of great importance and often gives us a better understanding than any text description). I kindly ask you to shorten the tables to 10-20 rows. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      I'll look into your task.

  10. This was extremely useful! Thank you so much!

    The equations with provided examples was so much clearer than other "support" I had found previously.

  11. Hi, is it possible to include importrange with sumif? I wanted to sum data from a spreadsheet, criteria is date and sum range is amount. I would like this to be entered into a different spreadsheet.

    • Hi Peesot,

      My apologies for the late reply, I somehow missed your comment.

      Well, at the moment SUMIF does work with IMPORTRANGE but only if first you transfer the required range with a separate IMPORTRANGE and give the function permissions to access another spreadsheet. Otherwise, your SUMIF will return 0.

      You will find the tutorial on IMPORTRANGE in this blog post.

  12. Hi,
    I'm hoping you can help me with a formula.
    arrayformula with sum function a1+a2,a3+a4,a5+a6..................pair 2-2-2
    and a1+a2+a3,a4+a5+a6,a7+a8+a9....................Pair 3-3-3

  13. I'm hoping you can help! I've read through all of the queries above hoping someone would be trying to do the same thing as me and have searched other forums as well.

    Basically, I want a sum of the values in column J the value in column D is "Law" and the value in column F is "2021". The formula below returns a result of 0. What am I doing wrong?

    =SUMIF(AND('2021 EO Sales'!D:D,"Law",'2021 EO Sales'!F:F,"2021"),'2021 EO Sales'!J:J)

    • Hello Katy,

      You're trying to sum based on two conditions using SUMIF. The SUMIF function adds up based on one condition only. The AND function won't help there.
      To sum by several conditions, you should use SUMIFS instead. It's the function described in this blog post (see above). Please look through it closely and you will be able to build the correct formula.

  14. Hi,
    I'm hoping you can help me with a formula. I have attempted to look through many forums but not quite been able to find what I'm looking for.
    I am attempting to sum multiple columns with multiple criteria that also relies on criteria from other columns and one of those has multiple criteria as well.
    Below is the outcome I am looking for
    Desired result for cell I18
    SUM all columns S,W,AA,AE,AI,AM
    IF columns
    H = AA
    K = W
    J = Jul19|Aug19|Sep19|Oct19|Nov19|Dec19|Jan20|Feb20|Mar20|Apr20|May20|Jun20
    R,V,Z,AD,AH,AL = Jul19|Aug19|Sep19|Oct19|Nov19|Dec19|Jan20|Feb20|Mar20|Apr20|May20|Jun20
    Please see below the link to the spreadsheet - this could be restricted so please let me know if you are unable to open
    https://docs.google.com/spreadsheets/d/1sOSPZ0RNObSuEdGg1YvndN_alXu77AU0QGy7J2cE0hs/edit#gid=0
    I hope to hear from you. I am desperate!

    • Hi Nicole,

      Yes, the access to your spreadsheet is denied. For us to be able to help you, please share it with us directly: support@apps4gs.com. Please include a sheet with the result you expect to get as well.
      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.
      Thank you.

  15. Sumifs is very heard

  16. we have 3 columns as a "sum_range" .. but the results only shows 2 columns being count..

    any solution?

    thank you

      • where should we send the link to ?!
        it seems prohibited to post it here..

      • Thank you for sharing a spreadsheet, Pradipto.

        I can see that you refer to 3 columns with one criteria range. Please specify if you're trying to SUM the total when the name from S5 appears in at least one column (E-G) or in all three columns.

        If you need something other than that, please add the sheet with an example of what you're trying to get.

        • Thankyou so very2 much for responding, i'm sorry for the late reply..

          The idea is to get the total work for 1 person on certain date..
          They are working as team (of 2 or 3) .. depends on the jobs load..
          .. only 2 out of 3 columns get counted / recognized

          i'll share the latest Sheet soon..
          Thank you for your assistant .. i'm so happy for this.. :)

          • Dear Pradipto,

            Thank you for editing the spreadsheet.

            I added correct formulas to the column V and highlighted them green, please take a look. I had to add up multiple SUM functions since SUMIF doesn't work the way you need. SUMIFS also won't help, since it will look for names in all 3 columns at once.

            • Awesome !! Thank your very very much for your help.
              i will look into it a s a p..

  17. Hi,

    I'm trying to use the SUMIF/SUMIFS OR criterion with wildcards i.e. as in your example above but

    =SUM(ARRAYFORMULA(SUMIF(A6:A14, {"*apples*", "*bananas*", "*lemons*"}, B6:B14)))

    I can only seem to get this to work with a SUMIF and not a SUMIFS - when I translate the above formula into its SUMIFS equivalent:

    =SUM(ARRAYFORMULA(SUMIFS(B6:B14, A6:A14, {"*apples*", "*bananas*", "*lemons*"})))

    the formula does not work (it only respects the first criterion "*apples*".

    Am I doing something wrong? Or is there a reason this wouldn't work with SUMIFS?

    Thanks!

    • Hi Michael,

      As Svetlana stated above, Google Sheets SUMIFS function works with AND logic only. That is the reason why SUMIF is used for the OR logic instead.
      So I'm afraid you won't make SUMIFS work and will have to go with either SUMIF or SUMPRODUCT.

  18. =SUMIFS('p_s MastNum'!$F:$HF,'p_s MastNum'!$B:$B,$D$1,'p_s MastNum'!$C:$C,$F$1,'p_s MastNum'!$E:$E,$B$19,'p_s MastNum'!$F$3:$HF$3,$A20)

    is my formula

    I get a #Value error.

    If I remove the last criteria and use $F:$F as the range, it works. However, I want it to return/add the values in row 1 & 2. It is another filter I need to use.

    In Sheet "p_s MastNum"
    Row 1 & 2 are names
    Row 3 is a helper row joining the two names with a " "
    Col B = Month each row has a value
    Col C = Year each row has a value
    Col E = 1 of 7 headers in each row. There are more than 1 header in the column

    Please help.

    • Hello Rain,

      For me to be able to help you, please share an editable copy of your spreadsheet with us (support@apps4gs.com) with 2 sheets: 1) source data with a formula, 2) the result you expect to get.
      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.

  19. Gracias

  20. Im trying to do a formula, not sure if the ones above are suitable - essentially I want to have a total that is increased/deducted by whether or not a number in a cell has a yes or no in the cell next to it.

    Basically I have a "bills paid out or to be paid out" section listed and then want to select yes or no next to it to say if its been paid yet or not which will then auto update the "whats left to pay out this month". so cell A1 - Car Insurance B1 - £153 C1 Yes - by changing it to yes, it doesnt calculate this in total for the month, it excludes it. but if it said no, it will include it in the total left to go out?

    Hope this makes sense!

    • Hello!
      If I understand your task correctly, the following formula should work for you:

      =SUMIFS(B1:B10,C1:C10,"No")

      I hope this will help

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 :)