Google Sheets - SUMIFS, COUNTIFS, AVERAGEIFS - When Range is Between Dates or Cell Contains & More

Learn how to use SUMIFS, COUNTIFS, AVERAGEIFS formulas. This tutorial shows how to SUM, AVERAGE or COUNT with condition, total range between dates, total when cell contains text, use multiple criteria, SUMIFS, COUNTIFS, AVERAGEIFS from another sheet (tab), use greater than or less than criteria and much more.

[@jeffreytherkelsen964]

This is so helpful, all of your videos are. Thanks so much! There is magic in these cells.....

[@diozhics26]

Woah, I never thought of using the transpose and unique functions. Perfect

[@lazalazarevic6192]

Great lesson. Never used wildcards in these functions before, might come in handy. Thank you man. Cheers

[@reijelcueva5208]

Thank you for your tutorial! I have learned a lot. sometimes I used symbols "$" in criteria ranges to lock the cell in between dates.

[@dianncotterell5531]

Great video. You have the same voice as another youtuber that reached financial freedom. I really think it's you.

[@frankvidarte1988]

Thank you for the tutorial! I was missing the "&" symbol when adding from date ranges.

[@doctoraitch2158]

Really love your videos and I've learned so much from them, so huge thank you! There is something I cannot figure out though. Lets say i have multiple sales of the same product but I only want to return the price (prices different each time) from the first sale. Or, from the 3rd sale. I thought this would be some kind of sorting/lookup based on the sequential occurence from the date column. Example: Find the value of "price", when the product is "X", transaction is "SELL", occurence is 2nd (if first is 1st March 2021, 2nd would be first instance after that). Any help would be greatly appreciated. or if you could point me to a video where you have covered this already. Hope i've made sense. Thanks!

[@goodwaysprojects3035]

It's wonderful & lot of thanks. I was also looking for sum, average of back dates.

[@NaomiPeris]

This tutorial was great! But how do you add several transactions tabs together? Example, if I wanted to pull all the NIKE sales from 3 different tabs. Can this formula be adjusted to do that?

[@funzies8522]

Still a badass resource. Thanks Learn Google Spreadsheets guy!

[@NetSkillNavigator]

Amazing tuts ! Thank you soo much !!

[@ExcelGoogleSheets]

Thank You! Glad you like them!

[@gzboti]

Really useful, thank you!

[@ArmadusMalaysia]

Forgot to subscribe to you the other day. Happy I didn't lose your channel.

[@ExcelGoogleSheets]

Welcome back!

[@JeromeFe]

Does anyone knows if I can sumif cell value that has number according to text appears on their note? I want to sum all expenses that I added note “visa” so I don’t need to have 2 different cells for that

[@qualpexgmailcom]

can you explain how to get the minimum value in a select group of cells (not a range of cells) excluding the ones that have a value of zero? Thank you

[@fcallow2940]

Very useful, thanks :)

[@jeffbetancourt1033]

Awesome video help me out a lot with my work spreadsheet

[@hariiswoyo1995]

do you have tutorial for using countif across workbooks?

[@datumticorp7311]

this helped me indeed.

[@afroditawhite5524]

Thank you!!!!!!!!

[@dodadew1]

I have been trying to count the occurrences of numbers in a date range. the table has the date and 4 coloums for entering 4 numbers for that day. I want to get the occurrence of each number in the last 1month, 2months, 6months, 1 year etc

[@kenthunderz]

man this guy is good

[@fictionstudios6876]

I think I have been in the right place. Plz can you upload a video on using the Google sheet as database in Android studio app....

[@josephtoripotter6691]

Hi. I've watched a lot of your videos and they are awesome. I've got a problem I haven't been able to find an answer to yet. Referring to your video around 24:05 you have 2 columns, brand, and sales. I have the same thing but I have 3 columns for sales (Website sales, wholesales and cash sales) because I separate the different ways I sell. I want only 2 columns (Brand and Sales) and data validation so I can have a dropdown with the 3 sales options and it will change dynamically. What do you think?

[@josephtoripotter6691]

I found the solution. Sumproduct! below is my solution. the first section is the sum range. all following sections are criteria and this can be done any way you like (check Columns for criteria or rows) =SUMPRODUCT(($E$2:$F)*($B$2:$B=$H$3)*($C$2:$C=$I$3)*($D$2:$D=$H4))

[@s.m.aggies7220]

Great work, I have see many of your Videos, dang have you started your own Class, Course, because I will take the course.

[@viralvideos6949]

Thank you so much

[@googleguruji2553]

You can make your tutorial more useful by providing a link of relevant spreadsheet with datas and formulas for better practicing.

[@zakadx584]

Hai, In minute 3:18, SUMIFS with multiple Criteria (Date) , you code the date ">2/13/2017" how to if i just want to only with Month or all the date of the month, because in A column like you i have another Month thanks

[@javovalenzuela7263]

Thank you Sir

[@misssomod194]

Thank YOU

[@kefaliask]

Great one as always. When I use the date I get an error that is text not number!

[@anthonylouis6874]

So, I'm finding problems because I'm trying to use the "google forms time stamp" to sumif some values. Exemple, the time comes in standart format like 02/01/2020 10:20:48 ok then, I've tryed to compare as a ;"02/01/2020"&"*"; also ;"02*"; and even tryed "43832*" (the number mode for dates data). But I couldnt make the sum works.... I think the issue is related on the google forms time stamp format... is that a way to sum if the dates in this way? 02/01/2020 10:20:48 or in number date 43832,4325 (in case I want to disconsider the values after , like round numbers)

[@ExcelGoogleSheets]

use A:A,">=02/01/2020",A:A,"

[@myominthwin8721]

May I get the spreadsheet that was explained in the tutorial?

[@dianamazariegos8371]

Hey, how it would be an averageif formula but with a different workbook? Like mixing importrange or another way? Need help!

[@ExcelGoogleSheets]

You'll need to use SUMPRODUCT to get the sum and then divide it by count.

[@milofungus2982]

Thank you

[@n.c.6135]

How to work with minutes for any particular day ??!!! Guide me plz. 💐

[@ShafaliJain-nk8xt]

Nice Video

[@mvillalr]

Thanks, good video

[@ExcelGoogleSheets]

:)

[@lnanimateCarbonRod]

I am receiving error "array arguments to AVERAGEIFS are of different size. How can I averageIF across different tabs? my formula =AVERAGEIFS('Sheet1'!$D:$D,'Sheet1'!$B:$B,$C2,'Sheet2'!$D:$D,"desired phrase") Thanks in advance for the help. Please let me know!

[@stephentierney7257]

hi there, hope you can help me I need to know the following If any cells in the columns B6:H6 contain a number then I need the answer to be 1 if they are all empty then return 0 Hope someone can help me thank you

what if you want to quote the cell. For example not the date, but the cell that contains the date? 3:20

[@ExcelGoogleSheets]

Please share an example sheet. I don't understand what you need from your comment.

[@rakesh9840]

Hi,Can you help me in finding the number of calls done by employee through the response sheet which they submit everyday...using countifs & Today() . Kinda automated sheet to find ftd details.

[@ExcelGoogleSheets]

=COUNTIFS(A:A,">="&TODAY(),"

[@rakesh9840]

@@ExcelGoogleSheets Thanks...Will check this.

[@rakesh9840]

Hi, I have tried using the formala but getting answer as 0. Below is the format of response sheet. Date and timestamp - Employee email I'd - Partner Name - Call connected status.

[@go2gal247]

In my sumifs can you use the same column for 2 different choices? I need to filter out one of the choices in a column. For instance and I calculating income but if the file cancelled I dont want it to include those numbers in my totals. How can I accomplish that?

[@ExcelGoogleSheets]

in condition use "cancelled"

[@go2gal247]

@@ExcelGoogleSheets do i put the name that i dont want included in the ?

[@AustralianBushman]

Fkn love you man best Internet

[@ShafaliJain-nk8xt]

Can you please share the data set. thanks

[@theltrain458]

What if I had “Fred” in E2, and had “Ethel” a large amount of times in cells g2:aa100. How would I count the number of “Ethel” ONLY if “Fred” is in e2?

[@ExcelGoogleSheets]

Use IF function with COUNTIFS function inside.

[@theltrain458]

@@ExcelGoogleSheets thanks could you give an example?

[@ExcelGoogleSheets]

=if(E2="Fred",COUNTIFS(blahblah),"")

[@theltrain458]

@@ExcelGoogleSheets thanks so much. What if I wanted to only count instances of Ethel in g2:a100 when the corresponding cell in row e is Fred? Consider 100 rows and like half are fred

[@m.k.m.fernando2017]

I have a question about to use two names for single value on a data base. also need to reduce some values each by two names. as an example I enter new name or old name into data base and I need to reduce in one name. So how can I fix this issue please help me

[@ExcelGoogleSheets]

Remove duplicates?

[@m.k.m.fernando2017]

@@ExcelGoogleSheets if u could please give your email adress I'll send an exmaple sheet to describe you. Its a big help for me

[@m.k.m.fernando2017]

@@ExcelGoogleSheets cannot be done like it

[@cuneiformscript2665]

🙏🏻

[@nothankyou4016]

lets say for example Chester Harvey was the sales rep for New Balance and Nike (impossible but bare with me) and I wanted to count how many times it says Nike AND New Balance for only Chester, how would I do that? The reason I ask is I have hundreds of students who I need to check attendance for and I am considering "Late and Present" as the same thing. I want to see how many times Bobby was Late AND Present, how would I do that?

[@ExcelGoogleSheets]

You do 2 sumifs functions and add them together.

[@nothankyou4016]

@@ExcelGoogleSheets Thank you so much!

[@leonardomoleiro3946]

What if you wanted to sum NIKE and New balance?

[@ExcelGoogleSheets]

Add 2 sumifs =SUMIFS(nike stuff)+SUMIFS(NEW BALANCE stuff)

[@usmaniqbal1836]

Share link this file for download..

[@RajeshKumar-bl5yf]

Without any draging the data how to auto drag with using arrayformula

[@ExcelGoogleSheets]

You can only do that for SUMIF, COUNTIF, AVERAGEIF but not for SUMIFS, COUNTIFS, AVERAGEIFS

[@DeepakSharma-ce6qe]

How to handle #N/A while using AverageIFS

[@ExcelGoogleSheets]

=IFERROR(AVERAGEIFS(stuffhere))

[@usmaniqbal1836]

Why are you not share the Excel File for practice.. Shame on you