Google Sheets - SUMIFS, COUNTIFS, AVERAGEIFS - When Range is Between Dates or Cell Contains & More
HTML-код
- Опубликовано: 19 окт 2024
- 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.
I've been looking for a way to perform the function of SUMIFS for a long time. This video is excellent and a huge time saver. Thank you for putting these guides together!!!!
Great video. You have the same voice as another youtuber that reached financial freedom. I really think it's you.
Woah, I never thought of using the transpose and unique functions. Perfect
This is so helpful, all of your videos are. Thanks so much! There is magic in these cells.....
Still a badass resource. Thanks Learn Google Spreadsheets guy!
Forgot to subscribe to you the other day. Happy I didn't lose your channel.
Welcome back!
Thank you for your tutorial! I have learned a lot. sometimes I used symbols "$" in criteria ranges to lock the cell in between dates.
Awesome video help me out a lot with my work spreadsheet
Great lesson. Never used wildcards in these functions before, might come in handy.
Thank you man.
Cheers
Amazing tuts ! Thank you soo much !!
Thank You! Glad you like them!
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?
You can make your tutorial more useful by providing a link of relevant spreadsheet with datas and formulas for better practicing.
May I get the spreadsheet that was explained in the tutorial?
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
Thank you for the tutorial! I was missing the "&" symbol when adding from date ranges.
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
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!
Great one as always. When I use the date I get an error that is text not number!
man this guy is good
do you have tutorial for using countif across workbooks?
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!
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?
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))
It's wonderful & lot of thanks. I was also looking for sum, average of back dates.
Great work, I have see many of your Videos, dang have you started your own Class, Course, because I will take the course.
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?
in condition use "cancelled"
@@ExcelGoogleSheets do i put the name that i dont want included in the ?
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
Hey, how it would be an averageif formula but with a different workbook? Like mixing importrange or another way? Need help!
You'll need to use SUMPRODUCT to get the sum and then divide it by count.
Really useful, thank you!
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?
You do 2 sumifs functions and add them together.
@@ExcelGoogleSheets Thank you so much!
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)
use A:A,">=02/01/2020",A:A,"
Can you please share the data set. thanks
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
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?
Use IF function with COUNTIFS function inside.
@@ExcelGoogleSheets thanks could you give an example?
=if(E2="Fred",COUNTIFS(blahblah),"")
@@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
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
Remove duplicates?
@@ExcelGoogleSheets if u could please give your email adress I'll send an exmaple sheet to describe you. Its a big help for me
@@ExcelGoogleSheets cannot be done like it
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....
what if you want to quote the cell. For example not the date, but the cell that contains the date? 3:20
Please share an example sheet. I don't understand what you need from your comment.
Nice Video
Thanks, good video
:)
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.
=COUNTIFS(A:A,">="&TODAY(),"
@@ExcelGoogleSheets Thanks...Will check this.
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.
Thank you so much
What if you wanted to sum NIKE and New balance?
Add 2 sumifs =SUMIFS(nike stuff)+SUMIFS(NEW BALANCE stuff)
Without any draging the data how to auto drag with using arrayformula
You can only do that for SUMIF, COUNTIF, AVERAGEIF but not for SUMIFS, COUNTIFS, AVERAGEIFS
Thank you!!!!!!!!
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
Very useful, thanks :)
Thank you Sir
Share link this file for download..
Thank YOU
Thank you
How to handle #N/A while using AverageIFS
=IFERROR(AVERAGEIFS(stuffhere))
this helped me indeed.
Fkn love you man best Internet
🙏🏻
Why are you not share the Excel File for practice.. Shame on you