by 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:
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:
- 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.
- 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.
- 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!
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 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 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,"<>")
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)))
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:
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:
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:
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:
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:
Working from the inside out, here's what you do:=(SUMPRODUCT(--(C2:C13=F2), (--(ISNUMBER(MATCH(A2:A13, F1:G1, 0)))), B2:B13))
How this formula works
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).
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.
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)
188 comments
I used SUMPRODUCT with SUMIFS in excel to sum up values in one column based on two other columns and works correctly. I moved this Excel to Google Sheets and opened it with Google Sheets to update it. The calculations in one cell are returning zero (0).
The formula is (=SUMPRODUCT(SUMIFS('Income 2024'!$F$3:$F$41,'Income 2024'!$D$3:$D$41,'Income-Expense Types'!B3:B5)) where:
Column F of sheet (tab) "Income 2024" contains all income amounts
Column D of sheet (tab) "Income 2024" contains the type of income. For example rental income from property 1,2 or 3, Other incomes like interest etc.
Column B of sheet (tab) "Income-Expenese Types" contains the income types. For example rental incomes from three properties in cells 3 - 5, Interest income etc.
What I wanted to sum up is the total income from the three properties, Interest income etc. In it's current state the formula is returning zero (0). instead of the total amount for the three properties. When I remove the range reference in the last criteria "Income-Expense Types'!B3:B5" and use only one cell say B3 it returns the total amount of the selected criteria.
Does anyone have an idea how to fix this issue?
Example data:
Sheet: Income-Expense Type Sheet: Income 2024
Column B. Column D. Column F
Income Property 1 Rental Property 1 $1000
Income Property 2 Interest Income $ 250
Income Property 3 Other Income $550
Interest Income Rental Property 3 $3000
Other Rental Property 2 $2500
Rental Property 1 $1000
Rental Property 3 $3000
Rental Property 2 $2500
Other Income $6550
You are facing the same issue that the previous commenter (Kevin) had. You can't use an array as a criterion in SUMIFS. In your case, you'll need to create separate SUMIF formulas for each property.
To streamline the process, I recommend using a Pivot Table. You can learn more here:
🔗 https://www.ablebits.com/office-addins-blog/google-sheets-pivot-table/
Alternatively, you can try the Combine Duplicate Rows add-on ( 🔗 https://www.ablebits.com/docs/google-sheets-combine-duplicate-rows/), where your key column will be Column B – the Property type.
In my previous entry the data formatting did not show correctly when posted.
There are two sheets involved
Sheet 1: "Income 2024" contains values in column D containing the income Types (Rental income property 1, Rental income property 2, Rental income property 3, Interest income etc....) Column F contains the dollar amounts
Sheet 2: "Income Expense Types" is a data entry validation drop down selection which contains Property 1, Property 2, Property 3, Interest income, Other Income in column B
Hi, I would like to ask for assistance with my sheets for collection and expenses. I can't seem to find a solution from examples mentioned.
Column A2:A contains description such as: Bill, Share, Extra, Fund
Column B2:B is list of Names
Column C2:C is Months name: January, February, March ...
Column D2:D is the amount to be summed.
This formula I'm currently working only sum up the first criterion which is "Share" and disregard the second, "Extra". My idea is to make make sumifs as short as possible, thus avoids adding two sumifs formula.
=SUMIFS(D2:D, A2:A,{"Share","Extra"}, C2:C,"March")
As far as I understand you're trying to sum amounts for both "Share" and "Extra" in March. Since SUMIFS doesn’t handle I offer a couple of better approaches.
1. Use the QUERY() Function Instead
If you task is to summarize totals for "Share" and "Extra" in March, QUERY() is a better option. Based on your example, I recreated the formula with the QUERY() function:
=QUERY(A:D, "select A, B, sum(D) where (A = 'Share' or A = 'Extra') and C = 'March' group by A, B")
🔗 Learn more about using QUERY() here: https://www.ablebits.com/office-addins-blog/google-sheets-query-function/
2. Use Our Combine Duplicate Rows add-on
- Set Columns A and C as key columns (Description and Month).
- Set Column D (Amount) to be merged using the SUM function.
It'll instantly group descriptions and sum amounts month by month.
🔗 Try it here for free: https://www.ablebits.com/docs/google-sheets-combine-duplicate-rows/
If you're aiming for something slightly different or need to break down the task further, feel free to reply with more details. We’re happy to help!
I need to use a formula, but I'm not sure just which one I need or how to use it.
I have a sheet of data with month and year summaries at the top, and the raw data starting below.
My boss wants to get a visual representation of a "trend", so he wants me to put the month and year dates one by one in a vertical column (cells A1:A340). He wants the sum for each month within each year in the next column (cells B1:B340).
The raw data has a date in column B (cells B501:B2500), and the range to be summed is in column D (cells D501:D2500).
How can I sum the raw data (cells D501:D2500) by month and year?
Hello Henry,
If I understand your task correctly, you need to use the MONTH() and YEAR() functions in your SUMIFS conditions.
If you'd like to get help with a formula, 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. I kindly ask you to shorten the tables to 10-20 rows.
Note. The result sheet is of great importance as it gives us a better understanding than any text description.
Note. We keep that Google account for file sharing only and don't monitor its Inbox. Do not email there. Once you share the file, just confirm by replying to this comment.
I'll look into it and try to help.
I want to get the total amount in Column D in Tab1 sheet on basis of multiple criteria that needs to be searched in multiple columns in data tab. attaching a example sheet. The formula should be consistent formula that should work. I wanted to create a dynamic range that is creating formula by searching the search key in role column and preparing a range which then act as a criterion range to SUMIFS formula. I tried various methods such as :
Match+index + Indirect formula to generate that manual automatic range but its not working
https://docs.google.com/spreadsheets/d/1-dDR3Ve9pk4so_JH5wN8NNUIfrzY361ZivdnQFqVWQI/edit?gid=0#gid=0
Could you please help with a formula in Column D "Total Sales" of tab1 that remains consistent in all cells ?
Hello Vidhey,
For me to be able to help you, please clarify if you want to sum the corresponding numbers or just fetch the first matching record. I'm asking because your 'Expected result' column features the first occurrence for 'ABC01, Account Executive' but then some strange number for 'ABC09, Sales Manager' and then 0 for ABC04, Account Executive even though there are matches.
The formula for 'SUMIFS with OR criteria (multiple criteria in different columns)' just saved me. You rock. Thank you!!!
We're pleased Svetlana's article has helped you! 😊
Hello,
I am trying to build a formula but I'm not quite sure how to do it.
I want to automatically add up my expenses based on whether or not a checkbox is checked, but I also want to filter the criteria to a specific month.
My current (working) formula is =sumif(ARRAYFORMULA(TEXT(A3:A,"MMM-YYYY")),N7,G3:G) which gets me all of the numbers for the whole month (expenses and income) but I have 2 columns of checkboxes (Column I for income and Column J for expenses) that I want to add another criteria of if the expenses checkbox column is checked being the only cells that are being summed. But I also want them to be summed up based on the date range input from the formula above. I want to place these formulas in different boxes based on the date so I can compare one months expenses to another months expenses.
Is this possible?
Thank you.
Hello Ben,
For me to be able to assist 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 as it gives us 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. Do not email there. Once you share the file, just confirm by replying to this comment.
I'll look into your task and try to help.
Buenos días,quisiera pedirte ayuda con la fórmula sumar.si.conjunto, la quiero usar en un inventario.
Producto. Cantidad. Movimiento. Producto. Cantidad inicial. Cantidad actual. Fecha v
Harina 2000. Salida. Harina. 10000. Form sumar si cjto( debería sumar o restar dependiendo si es entrada o salida).
Lo ví en un tutorial pero es antiguo y cambio la forma de escribir y me da error, error analisis , value. No sé cómo corregir. Agradezco tu ayuda.
Hello Gladys,
Sorry, we provide help in English only. If you're having difficulties with your SUMIFS formula, please reply with the exact formula you're using and specify the text of the error you see when hovering your mouse over the formula. I'll try to help.
Hello, please help:
COLUMN1 COLUMN2
Adult Dog 1
Adult Cat 2
Baby Dog 3
Baby Cat 4
Senior Dog 5
Senior Cat 6
How would I use SUMIFS to calculate, for example total Adults, or total Babies, BUT using a condition to sum the numbers in COLUMN2 by EXCLUDING those rows which have a certain string? Example, count all dogs except Senior, or count all animals except Baby Cat? Thanks you!
Hello Slava,
I can see 4 different tasks in your comment. For example, to sum all adults, the following formula will do: =SUMIFS(B2:B7, A2:A7, "*Adult*")
The same for babies.
To count all but seniors, you could use this one: =SUM(B2:B7)-SUMIFS(B2:B7, A2:A7, "*Senior*")
The same for all but baby cats.
Hello,
I am trying to build a workout summary for my workouts.
I have the raw data going into a tab "workouts" - This has "submission date" for the date of the workout entry.
Column B is type of workout (pushups, pullups, etc)
Column C is "reps"
Column D is "weight"
Column E is "time"
Reps, weight and time repeat to represent up to 10 "sets" of exercise. (Final column is AF)
I am trying to build a second tab, "workout summary", representing 1 week in each row, starting with 1/1/2024 as A2 for example, then 1/8/2024 in A3...
I want this to be a summary of total reps of each exercise done each week.
So if column A is "Week", Column B is "Pullups", Column C is "Pushups", etc.
I am trying to build a formula to sum the total number of pullups I did during the week, based on the data entered in "workouts"
Thanks in advance!
Hello Heath,
Sorry for the late reply, we were on holidays. If you still need help with your task, for me to be able to assist 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. I kindly ask you to shorten the tables to 10-20 rows.
The result sheet is of great importance as it gives us a better understanding than any text description.
Note. We keep that Google account for file sharing only and don't monitor its Inbox. Do not email there. Once you share the file, just confirm by replying to this comment.
I'll look into your task and try to help.
I am a teacher and attempting to track data on Math Tests and Test Retakes using a Google Spreadsheet. I have set up a sheet that does basically everything I want, but I am having trouble with some "if this, then this, and then this" type statements to get my final data.
Column A = Student #
Column B = Score on original test
Column C = Score on the test retake
Column D = Score change from original test to retake test
Since my school is grading using Standards Based Grading, the students receive a 1, 2, 3, or 4 on a test. This means all of the scores in Column B will be either a 1, 2, 3, 4. I then total all of the students who receiveda 4 on the original test, 3 on the original test, etc. For instance, there are 30 students and 16 of them scored a 4 on the original test, 10 of them scored a 3, and 4 of them scored a 2. The "Received a 4" total will be 16 (there were 16 students that received a score of 4 on the test), Received a 3 total will be 10, and the Received a 2 total will be 4.
If a student scores a 1 or a 2 on the orignal test, he/she is forced to retake the test after reteaching and allowed the opportunity to increase his/her score. If a student scores a 3 on the original test, he or she can still retake the test in order to improve his/her score. I track all of the retake scores in Column C on my spreadsheet and total all of the scores of 4 on the retake, 3s, 2s, and 1s using the same method as listed above.
Where I struggle in my formulas is trying to do something like this ---- if the score in Column B is greater than the score in Column C, then use the score in Column B to total up the number of 4s, 3s, 2s, and 1s in Column B. BUT, if the score in Column C for a particular student is greater than the score in Column B for that student, I want to use the score in Column C in the total for Column B. For instance, let's take two students, #1 and #2. Student #1 scores a 4 on the original test and student #2 scores a 2 on the original test. The "Received a 4" total will be 1 and the "Received a 2" total will be 1. Student #2 retakes the test and scores a 3. I want my formula to look at the score in Column B and Column C and take the larger of those scores to use in the computation for the totals. In this instance, after the retake, I would have the following results: "Received a 4" = 1 and "Received a 3" = 1. The score of "received a 2" would no longer be present as the system did not total up the score for student #2 from Column B because Column B was lower than Column C. Since Column C had a higher score, the formula used the total from Column C to compute the overall totals.
I hope I am relaying what I desire correctly because I see it clearly in my mind. My guess is, there is a formula that can do this and I hope you all can help me. Thank you for your time and your effort on this task of mine as it has me stumped.
Hello Trigg,
Thank you so much for the detailed description of the task! Looks like a formula like this will help you out:
=COUNTIF(ARRAYFORMULA(IF(B2:B30 >= C2:C30, B2:B30, C2:C30)), 4)
It's for those who got 4 as a final result. Just make the copies and replace 4 with 3, 2 and 1 to get the count of students for other scores.
Is it possible to apply a function to the criterion range in SUMIFS?
Something like:
=SUMIFS(Transactions!$C:$C, month(Transactions!$B:$B), 11)
which doesn't work (gives: Array arguments to SUMIFS are of different size.)
Hi Tim,
I was solving a similar task and managed to make MONTH work by wrapping it in ARRAYFORMULA. Sorry I didn't think of this solution earlier.
Hi Tim,
It is MONTH that doesn't work with the range in this case.
Hi,
I am trying to return a sum by using SUMIFS, I have a requirement to use multiple criterion (S6:S17) so I used it with SUMPRODUCT but the result is zero. The formula is working fine with single criterion. Kindly advice
=SUMPRODUCT(SUMIFS(L:L,D:D,T6,A:A,S6:S17))
Hi Ali,
Try to modify your formula as shown in this part of the article.
Hello. Is there a way to use SUMIFS to add together numbers from multiple columns but based on criteria from other multiple columns? Even more complicated, can it be done in a header formula (like an array formula) that will work for an entire column?
Here's a very basic stripped down version of what I'm trying to do as an editable test sheet that is hopefully self-explanatory...
https://docs.google.com/spreadsheets/d/158wOjuhqNTOtR9TvE3I3t7356WQ7IHe2srtF2e50luc/
Thank you!!
Hello CC,
Sorry, your comment's got lost among all others. Do you still need help with the task?
i need to sum the number of quantity with the 2 criteria; check in and out and product name. the output should be number of check in of particular product. You missed to include an additional criterion in sum ifs with or & and.
Hi! Without seeing your data, it's hard to offer you a formula. I think the examples in the article above will help you.
Hi, I am trying to return a value based on two criteria. I have 3 columns of data. One column has the date, another column a numeric value and another column a letter denoting a code. I would like to display on another sheet the numeric value when the month in the column and the column with the code match it will return that value.
column 1 column2 column3
6-30-23 $1
7-31-23 $7 d
so if column 1 = month 7 and column 3 =d return the value of $7 to another sheet
I am not sure how to accomplish this. I would greatly appreciate any help, thanks so much!
Hi Tony,
It looks like you need to use the IF function for the task, with the AND logic specifically.
To add month as a criteria, use the MONTH function within IF.
Hello Natalia:
Can you help shed light on the correct function for (I think Sumif) for this problem:
ColB ColC ColD ColE ColF ColG ColS ColT
Fruits Qty Fruits Qty Fruits Qty Unique Fruits Qty Unique Fruits
Row12 Apples 15 Plums 3 Figs 18 Apples ??
Row13 Oranges 8 Coconuts 3 Grapes 9 Apricot ??
Row14 Dates 7 Peaches 8 Apples 9 Bananas ??
Row15 Figs 12 Cherries 24 Apricot 12 Cherries ??
Row16 Apricot 9 Bananas 9 Oranges 8 Coconuts ??
Row17 Peaches 14 Kiwi 12 Bananas 9 Dates ??
Row18 Apples 5 Peaches 6 Figs ??
Row19 Cherries 7 Grapes ??
Row20 Kiwi 8 Kiwi ??
Row21 Oranges ??
Row22 Peaches ??
Row23 Plums ??
For Column S I used function to sort the unique fruits: =sort(unique(filter(FLATTEN(B12:B20,D12:D20,F12:F20),FLATTEN(B12:B20,D12:D20,F12:F20)"")))
How do I sum for Column T?
Thanks in advance for your help.
Hello Eric,
Please share an editable sample spreadsheet with this data with us directly: support@apps4gs.com
Please include your SORT formula and the result you'd like to get. I'll look into your task and try to help.
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 am trying to do a SUMIFS formula. Situation is this.
I need the sum of several different cells (dollar amounts) to fall between the dates 1/1/2022 and 1/31/2022. Dates are speified in the column right before it (D:D for $$ and C:C for dates) I have this right now =SUMIFS(D:D, C:C ">=01/01/2022", ">=01/31/2022")
Please help
Hello Jacalin,
You missed a comma and one argument. Please pay attention to this part of the article to build the formula correctly.
HI,
I am using this formula with two regular conditions and then an array for an OR condition for that list. Here is the formula
=(SUMPRODUCT(--(DB_Date=$A$7)*(--(DB_Rev_Type=$F$1)), (--(ISNUMBER(MATCH(DB_Customer, filter(DB_Customer,DB_Date=$F$4,DB_Rev_Type=$F$1,DB_ARR=0), 0)))), DB_ARR))
What I am missing is the ability for cell F1 to be changed to "*" and the formula include all values in this condition (DB_Rev_Type=$F$1)
Thanks
Hi Matt,
Please try this formula:
=ARRAYFORMULA(SUMPRODUCT(--(DB_Date=$A$7),--(IF($F$1="*",IF(DB_Rev_Type<>"",TRUE,FALSE),DB_Rev_Type=$F$1)),--(ISNUMBER(MATCH(DB_Customer,FILTER(DB_Customer,DB_Date=$F$4,DB_Rev_Type=$F$1,DB_ARR=0),0))),DB_ARR))
I'm trying to do a sumifs with conditions, i have been trying for 2 weeks now, moving around parts, but i can't make it works!
I would love to have a bit of help, i can send my spreadsheet. Thank you.
=if(isblank(C5),"",
if(SUMIFS(Dividends_Liste!$M:$M,Dividends_Liste!$B:$B,$R$2,Dividends_Liste!$C:$C,$F$4,Dividends_Liste!$E:$E,C5,Dividends_Liste!$G:$G,$B$2,Dividends_Liste!$H:$H,$C$2)>"0",
if(search("Janv",D5),SUMIFS(Dividends_Liste!$M:$M,Dividends_Liste!$B:$B,$R$2,Dividends_Liste!$C:$C,$F$4,Dividends_Liste!$E:$E,C5,Dividends_Liste!$G:$G,$B$2,Dividends_Liste!$H:$H,$C$2)),
SUMIFS(Dividends_Liste!$M:$M,Dividends_Liste!$B:$B,$R$2,Dividends_Liste!$C:$C,$F$4,Dividends_Liste!$E:$E,C5,Dividends_Liste!$G:$G,$B$2,Dividends_Liste!$H:$H,$C$2)))
Or even something like that: =if(isblank(C5),"",
iferror(IF(regexmatch(D5,"May"),SUMIFS(Dividends_Liste!$M:$M,Dividends_Liste!$B:$B,$R$2,Dividends_Liste!$C:$C,$J$4,Dividends_Liste!$E:$E,C5,Dividends_Liste!$G:$G,$B$2,Dividends_Liste!$H:$H,$C$2),
if(SUMIFS(Dividends_Liste!$M:$M,Dividends_Liste!$B:$B,$R$2,Dividends_Liste!$C:$C,$J$4,Dividends_Liste!$E:$E,C5,Dividends_Liste!$G:$G,$B$2,Dividends_Liste!$H:$H,$C$2)>0,
SUMIFS(Dividends_Liste!$M:$M,Dividends_Liste!$B:$B,$R$2,Dividends_Liste!$C:$C,$J$4,Dividends_Liste!$E:$E,C5,Dividends_Liste!$G:$G,$B$2,Dividends_Liste!$H:$H,$C$2),"0"),"nul")))
What i want to do is: if month present in "D", i want the result of formula "sumifs" or "0" if no data yet, but if the formula "sumifs" is >0, without having the month in "D", i still want the result of the "sumifs" formula showing.
And if no month and formula "sumifs"=0, then i want "nul" as a result
Hello Angel,
Yes, please share an editable copy of your 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 as it gives us a better understanding than any text description. 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 it and try to help.
Thank you for your fast reply, i sent a sharing link to your email. You have 3 sheets inside and the same formulas that i wrote here.
I hope it's understandable.
I want the main formula "sumifs" to be excecuted even if there is the month written or not. But if the month is in the future written, i want the result to be "nul" like that i can format and have "prediction" colors.
I even try with the today's date, but in one of my sheet it's 01/01/1900 for months so today will always be older than any days (even if i use month only and not full date)
I hope it's not too confusing...
Thank you very much
Angel,
I can see that your expected results are the same as the actual formula results. If you still need assistance, please adjust the numbers in your expected results accordingly.
I filled up all the chart, and the formula doesn't give the result i want. (only 0 everywhere)
The formula totally works but it's not what i want.
I tried to explain in a better way what i want underneath the table, let me know if you can find a solution or not.
Thank you very much for your time.
Thank you for specifying it, Angel.
I duplicated the sheet and put the formulas to Jan-Apr (F5:I7). F5 is different from your expected result because there's no Jan in D5, so I guess you just filled it incorrectly in the example. Please fill cells for other months accordingly.
Hope this helps :)
Thank you for sharing the file, Angel, I've got it.
I'll look into it and try to find a solution asap.
How can I include a cell reference date range in this formula.
=SUMIF($AU$2:$AU,A21,$AO$2:$AO)+SUMIF($AU$2:$AU,A24,$AO$2:$AO)
Hello Steve,
Sorry, what do you mean by a cell reference date range? Can you give an example and explain the task in more detail?
Hi Natalia,
We have a sheet with multiple tabs. We want one cell in one tab (Tab 1) to capture the number from cells in a column (example, column D) from another tab (Tab 2) only if certain conditions are met in other columns in Tab 2.
For example, cell A5 in Tab 1 will add all the numbers in column D of Tab 2 if column A of Tab 2 contains "apple" and column B of Tab 2 contains "Bread" and column C of Tab 2 contains "Flower". In addition, the information in columns A, B and C of Tab 1 are dropdown menus.
Is it possible with SUMIFS?
Hi Diego,
Based on the description, I believe it's possible. Only the formula will work with the values selected in those drop-downs (hidden values won't be considered).