What is the SUMIFS formula if I want to sum both NIKE and NEW BALANCE. Is it =SUMIFS(SUM RANGE, CRITERIA RANGE, CRITERION1, CRITERION2) or =SUMIFS(SUM RANGE, CRITERIA RANGE, CRITERION1, CRITERIA RANGE, CRITERION2). I tried both and it didn't work.
Now I better master the function Sumifs.Thank you for your clear explanation and by guiding us step by step (After understanding and testing successfully with my example I came back on your channel and suscribed.
Subscribed. Very clear narrative. The shortcuts taught are gems too. By the way, what if another condition is put into the formula. Eg. I want the sales total BUT NOT to include sales from CA. Please show how to go about it.
I need help. I have a long list in one column, that says something like over 3.5 1,42 under 3.5 2,90 These are copied odds from a bookmaker that I want to paste into a spreadsheet to compare with other bookmakers. Nearby in the document, I have a column that says: Over 2,5 Under 2,5 Over 3,5 Under 3,5 Over 4,5 Under 4,5 ...and so on In the column to the right of that column, I was the odds to be shown. So for this example, I want the cell to the right of "Over 3,5" to return the value of 1,42 - since that's the odds of over 3,5 found in the pasted column nearby. In other words, I want to use a function that says: (For the entire column I have copied with information, I want this cell to be the value of the cell in that column that is located 1 cell below another cell that says "3.5", and two cells below a cell that says "over". If such a scenario doesn't exist, leave this blank) In my dream scenario, I can copy the odds from a given bookmaker into my spreadsheet, and it will automatically sort out the odds relative to the correct cells next to my own column with information about markets. Can anybody help med with a function like that?
Pls I need help on the use of checkbox and sun. For instance if I want only checked row added together in another column. I believe you should get what am saying...
Hi, I have these example =sumifs('Registro Extras'!I3:I415;'Registro Extras'!C3:C257;F7;'Registro Extras'!F3:F257;U34) I want use sumifs but the criterias are in different sheets of the same file, but it results in · VALUE , what is wrong?
Is it possible to use SUMIFs to search multiple columns for the same criteria? For example, if you had multiple columns of different sales types (retail, wholesale, etc) for New Balance - Could you search multiple columns to total all sales for "New Balance"?
Hi thx for sharing. Is it possible to use the importrange function together with the sum if function in between sheets. I have tried but it comes out as an error. Tried querry function didnot work either.
Quite helpful.. I would like to change the cell reference based on a monthly sheet in the workbook without have to change all the cell references. For example my current formula is =Sumifs('Jan19'!$X$2:$X$400,'Jan19'!$C$2:C$400,D13). I would to have 'Jan19' reference a cell which would have another sheet for "Dec18" that is based on the month placed in let's say cell c10. I tried this but it diddn't work =Sumifs(c10!!$X$2:$X$400,'C10!'!$C$2:C$400,D13) with C10 being where I type in the new month. Jan19, Feb 19 etc. Thoughs?
Is there a way to use sumifs to sum ranges based on criteria, but one of the criteria is an exclusion, like sum a number that is in criteria 1 but IS NOT criteria 2? Thanks in advance.
@@ExcelGoogleSheets that's interesting. Work that work to make an exclusionary criteria type thing as well? And/or have you made a video about it already that will help me understand? Your videos are really good for explaining how the functions work, in a way that I understand how it works so I can use it in my own way without just copying and pasting your exact functions to get the desired results.
What if I want the sum of New Balance and Adidas from Midwestern and Northwestern? What will the formula look like? Currently I'm adding the SUMIFS, using =SUM(SUMIFS bla bla bla)+SUMIFS bla bla bla)+SUMIFS(bla bla bla). My formula became extremely long since I'm adding up multiple criterias from multiple columns
Hi Eugene, Yes, like this... =sum(ArrayFormula(sumif(E:E,{"ADIDAS","NEW BALANCE"},F:F))) ...or... =sum(filter(F:F,(E:E="ADIDAS")+(E:E="NEW BALANCE"))) James :)
This is really good. Glad I subscribed. I am trying to do the SUMIFS in a document with 2 dropdowns as the conditions. I can't tell if google sheets just doesn't perform this function with dropdowns or if I am doing it wrong. I've been trying different solutions, so maybe I'm just a bit confused by now and entering the syntax incorrectly.
I have the same question. Also, if you lock the cell range does that mean that you can't add rows? What happens when you add rows? Does that move the last rows out of the range so they are no longer part of the sumif calculation?
Unless there is other logic to figure out why the background color is chosen, this won't happen without a script. You should be able to do this using apps script though.
By far the clearest and most applicable lessons on spreadsheets I've found online. Thank you.
Wow, thanks!
The best teacher on the NET, I'm so thankful.
Question here: If i want multiple criteria here that is text based, how do I go about that? ie: sales of both New Balance and Adidas
What is the SUMIFS formula if I want to sum both NIKE and NEW BALANCE. Is it =SUMIFS(SUM RANGE, CRITERIA RANGE, CRITERION1, CRITERION2) or =SUMIFS(SUM RANGE, CRITERIA RANGE, CRITERION1, CRITERIA RANGE, CRITERION2). I tried both and it didn't work.
Now I better master the function Sumifs.Thank you for your clear explanation and by guiding us step by step (After understanding and testing successfully with my example I came back on your channel and suscribed.
Thanks bro, you're a very good teacher. I was able to configure it before I finished watching the video. Keep it up with your tutorials
Instablaster.
Subscribed. Very clear narrative. The shortcuts taught are gems too. By the way, what if another condition is put into the formula. Eg. I want the sales total BUT NOT to include sales from CA. Please show how to go about it.
is it possible to get them in a specific month only
That helped me so much, Thank you for taking the time to create the video
👍
I need help.
I have a long list in one column, that says something like
over
3.5
1,42
under
3.5
2,90
These are copied odds from a bookmaker that I want to paste into a spreadsheet to compare with other bookmakers. Nearby in the document, I have a column that says:
Over 2,5
Under 2,5
Over 3,5
Under 3,5
Over 4,5
Under 4,5
...and so on
In the column to the right of that column, I was the odds to be shown. So for this example, I want the cell to the right of "Over 3,5" to return the value of 1,42 - since that's the odds of over 3,5 found in the pasted column nearby. In other words, I want to use a function that says:
(For the entire column I have copied with information, I want this cell to be the value of the cell in that column that is located 1 cell below another cell that says "3.5", and two cells below a cell that says "over". If such a scenario doesn't exist, leave this blank)
In my dream scenario, I can copy the odds from a given bookmaker into my spreadsheet, and it will automatically sort out the odds relative to the correct cells next to my own column with information about markets. Can anybody help med with a function like that?
Pls I need help on the use of checkbox and sun. For instance if I want only checked row added together in another column. I believe you should get what am saying...
Hi, I have these example =sumifs('Registro Extras'!I3:I415;'Registro Extras'!C3:C257;F7;'Registro Extras'!F3:F257;U34) I want use sumifs but the criterias are in different sheets of the same file, but it results in · VALUE , what is wrong?
Range sizes must be the same. I3:I415 doesn't match C3:C257.
Got one off of my list of to do list. Thank you 👍
Helpful video, thanks.
Can you show how to use 'SUMIFS' to calculate 'DATA FROM MORE THAN ONE SHEET'?
love your videos! they are helping me automate my business. keep them comin'!
Great to hear!
Is it possible to use SUMIFs to search multiple columns for the same criteria? For example, if you had multiple columns of different sales types (retail, wholesale, etc) for New Balance - Could you search multiple columns to total all sales for "New Balance"?
No. Use DSUM instead.
hello could you help, how about if make with 3 conditions, Region Midwestern, Brand ADIDAS & CALVIN KLEIN, please help, thanks
Video coming up next week.
Thanks for sharing, that is helpful and clearly mind
Hi thx for sharing. Is it possible to use the importrange function together with the sum if function in between sheets. I have tried but it comes out as an error. Tried querry function didnot work either.
SUMIFS will not work with importrange, you'll have to use sumproduct or sum as array.
can i ask a question about sumifs? i write "
i find it. it must be use "
@@matematiksel 👍
Quite helpful.. I would like to change the cell reference based on a monthly sheet in the workbook without have to change all the cell references. For example my current formula is =Sumifs('Jan19'!$X$2:$X$400,'Jan19'!$C$2:C$400,D13). I would to have 'Jan19' reference a cell which would have another sheet for "Dec18" that is based on the month placed in let's say cell c10. I tried this but it diddn't work =Sumifs(c10!!$X$2:$X$400,'C10!'!$C$2:C$400,D13) with C10 being where I type in the new month. Jan19, Feb 19 etc. Thoughs?
Is there a way to use sumifs to sum ranges based on criteria, but one of the criteria is an exclusion, like sum a number that is in criteria 1 but IS NOT criteria 2?
Thanks in advance.
use "criteria"
@@ExcelGoogleSheets Thank you! The inside the quotations would never have occurred to me! It seems so unnatural lol. But it worked!
The whole criteria part in these functions is counter intuitive. I often end up using FILTER with SUM to avoid the idiotic way the criteria works.
@@ExcelGoogleSheets that's interesting. Work that work to make an exclusionary criteria type thing as well? And/or have you made a video about it already that will help me understand? Your videos are really good for explaining how the functions work, in a way that I understand how it works so I can use it in my own way without just copying and pasting your exact functions to get the desired results.
Thank you! Very useful video.
if the cell contains other letters or space then NEW BALANCE. please help
I want the criteria 2 base on date, how do I do that?
What if I want the sum of New Balance and Adidas from Midwestern and Northwestern? What will the formula look like? Currently I'm adding the SUMIFS, using =SUM(SUMIFS bla bla bla)+SUMIFS bla bla bla)+SUMIFS(bla bla bla). My formula became extremely long since I'm adding up multiple criterias from multiple columns
It's possible tu use an OPEN TERM in SUMIFS? Like "*yyyyyyyy*", but when this TERM is in a specific cell.
Just use concatenation. Like "*"&A1&"*"
How about using SUMIF or SUMIFS with multiply criteria in single range?
It's posible to sum: "New Balance" AND "ADIDAS"?
Hi Eugene,
Yes, like this...
=sum(ArrayFormula(sumif(E:E,{"ADIDAS","NEW BALANCE"},F:F)))
...or...
=sum(filter(F:F,(E:E="ADIDAS")+(E:E="NEW BALANCE")))
James :)
Great video, very instructional, but wayyyy too many ads, UA-cam. I really need to set up a Pi-Hole.
How to do SUMIFS for case sensitive scenarios?
Thank you for the detailed explanation!
This formula is not working on my google sheets.
Awesome.
Great help 🎉
Thanks! 😃
Thanks you for simple explaination
😀
For those using chromebooks with no F4 function keys, the solution is the search button (search+4) it will do the same to lock the cells.
Thanks for that!
This is really good. Glad I subscribed.
I am trying to do the SUMIFS in a document with 2 dropdowns as the conditions. I can't tell if google sheets just doesn't perform this function with dropdowns or if I am doing it wrong.
I've been trying different solutions, so maybe I'm just a bit confused by now and entering the syntax incorrectly.
Please watch this ua-cam.com/video/AKs3aEtaEKw/v-deo.html
How to get a running total using a arrayformula?
So helpful! Thank you!
thank you. Great video
If I have 3 conditions to sum
How ???
ua-cam.com/video/AKs3aEtaEKw/v-deo.html
i make like this
=SUMIFS(H$4:$H$30,$C$4:$C$30,"
try =SUMIFS(H$4:$H$30,$C$4:$C$30,"
why it not working, it say value
THANK YOU!!!
Very nice voice
Thanks! Simple, and even I understood it!! :))
how about summing cells by color
Great
What does locking the cells do?
I have the same question. Also, if you lock the cell range does that mean that you can't add rows? What happens when you add rows? Does that move the last rows out of the range so they are no longer part of the sumif calculation?
I also ask that.. What shortcut to locking the cell?
I wouldve loved to have the sheet data to follow along
Very useful, thanks!
Thank you for this video! It helps me a lot :D
How to SUMIf using BG color? can someone helpme out :(
Unless there is other logic to figure out why the background color is chosen, this won't happen without a script. You should be able to do this using apps script though.
Thank you
great vid but i have been on this for 1h and still get 0 at the end dont no whats wrong so going back to a calculator
woop woop for some reason it dont like the pound sign, work ok on 2018 accounts ?
Share a sample sheet
What a beauty
Awesome!
===== we get 15 but i get 13 way (if i sum (1,12,0.5,1.5
Thank you!
Thanks
🙏🏻
m9wwwd
Very helpful! Thank you!