SUMPRODUCT, SUM IF from Other Spreadsheets (files) - Google Sheets
HTML-код
- Опубликовано: 9 дек 2020
- Learn how SUMPRODUCT function works. See how to use SUMPRODUCT function to create conditional summing like SUMIF that works with data from other spreadsheets (Google Sheets).
#sumproduct #gsuite
This guy is so awesome! You make my day with anything Google Query or Userform.
The best channel of Google SHEET!
Awesome as always! Keep going! I'm looking forward to more google colab + google sheets videos, specially if it's possible to plot charts in google sheets using python.
Teacher...it blows my mind what one can do with a modern spreadsheet!
PS...I see your Auto Fill is working 👍
Great workaround! It will help me a lot! Have always avoided Sumifs
This video was excellent starting point for me to reference, THANK YOU! Sharing the below info I used for total across multiple google sheets bc I was unable to find this information online. My scenario attendance tracking, five weekly sheets, now needed monthly total sheet for each student's attendance broken out. Starting column B4, each student's name. Column I4, weekly attendance total. My Sheet page's name label have a space in between two words. If you have this you'll need to use 'Week 1'!B4:B with little ('). If not, use Week1!B4:B.
=SUMIF('Week 1'!B4:B, A4, 'Week 1'!I4:I) +
SUMIF('Week 2'!B4:B, A4, 'Week 2'!I4:I) +
SUMIF('Week 3'!B4:B, A4, 'Week 3'!I4:I) +
SUMIF('Week 4'!B4:B, A4, 'Week 4'!I4:I) +
SUMIF('Week 5'!B4:B, A4, 'Week 5'!I4:I)
=SUMIF(Week1!B4:B, A4, Week1!I4:I) +
SUMIF(Week2!B4:B, A4, Week2!I4:I) +
SUMIF(Week3!B4:B, A4, Week3!I4:I) +
SUMIF(Week4!B4:B, A4, Week4!I4:I) +
SUMIF(Week5!B4:B, A4, Week5!I4:I)
Your videos are good. Google must pay you for publishing such power-packed videos.
Keep it going. 👍👍
Great work again as always !!
Very detailed tutorial. started from very simple to make it more complecated step by step for better understanding. your way of teaching is very easy to absorb. best of luck
Dude you are a genius! This is so helpful!!!!!!!!!!
Thank you. Really it helps a lot.
Excellent class!
So helpful! Thank u sir
I was confused since i use sumif formula with import range in my worksheet but it doesn work. But u made it easly. Very mind blowing!
Awesome... This is helpfull! 👍
Very nice sir ❤
AMAZING VIDEO
Thank you so much 🥰
A very helpful video, thank you very much! I have a question though. If I have a data of sales and I have recipes, how do I generate a list of ingredients that has been used for the day / month? Thank you in advance and you are awesome!
Thanks a lot Sir ❤🙏🇮🇳
THANK
YOU
SIR!
Thank you so much
Very Helpful Videos Thank You Sir,
👍
Question!
What if I don't want to search for a specific name, but include all names that start with O?
You have an amazing method of explaining that makes everything easy. I was trying the same example without the need to import data since I have everything in one tab. I managed to add the results, but I can't get the names to work, any ideas?
Thanks!
Nice and good job. Put practiced file in the description video ....
halo, thank you so much for your knowledge, can you help me with this problem. i would like to do exactly like you do on the video, but instead of dragging the formula, i would like to use array formula instead. but it return "Array arguments to EQ are of different size." is there any solution or workaround about this?
Thank you so much, this was very helpful!
Hope that you could help me with a detail. I am referencing to another worksheet where the data is collected in monthly sheets called January, February, March etc. In the sheet that I am working in, I have stated the months names in cells D1, E1, F1 etc. So instead of entering "January!A:A", "February!A:A" in the IMPORTRANGE formula, I would like to reference D1 and E1 that says January and February. It would make it easier to drag out to the next column, but I can't get it to work. How do I do this? Thanks!
Hi all,
Any have idea how will be use sumproduct function in data studio. If any alternative suggestion for this.
Is posible sum several columns with sumproduct? But numbers no text
Hi, Is possible sum multiples columns with one conditions ?
Is it possible for Google Sheets to use the same cell for input and at the same time for displaying a formulated output?
like for example, i want to use A1 to input a value and the formulated output will be displayed in B1 and at the same time i can also use the B1 to enter input and the formulated out will displayed in A1.
I hope this will not confuse you.
No.
🙁
I need to use sumif on a main sheet that gathers information from all other sheets. Like, on the main sheet I wanna add all the numbers from the other sheets that contains a product like lets say "pencil". So it would gather the price for pencils on all other sheets and add up to show on the main page how much I spended with pencils... I found a video that show how to do this on Excel, but theres no content showing how to do on google sheets. it would get you a lot of views. The title of the video showing how to do on excel is "How to use SUMIF across multiple sheets in Excel?". please help with that
Pls make video ..how to modified import XML data ...column or match with rows name.
We are a flight department for a local company and we currently use Google sheets to get track of our trip sheets. What we are trying to do is create a summary sheet at the beginning of our workbook that contains all our trip sheets. The problem that I am having with creating a certain portion of our summary page is trying to figure out a formula that would take data from each sheet from row C24:H24 and add up the cells with "Charge" in the row above. I have gotten it to work using a sumif formula but I can only get it to work for one sheet only not being able to add multiple sheets together. Do you have any suggestions or is this even possible?
Probably possible with sumproduct and arrays but it all depends on how the end result should function & how many rows of data you have.
@@ExcelGoogleSheets Is there any way that would could converse about this so I would be able to provide screenshots of what we are wanting to do?
Is it possible to use this whole formula but instead of the text in the whole cell like here ("Olivia") we use only words that are contained in one cell?
For example, the text in the column is something like this:
Olivia eats fresh vegetables
Jennifer eats chocolate
Marko loves fresh fruit
John eats potato
Yanko make great fruit salad
And we want to search for words "fresh" AND "fruit".
Is this possible by doing the same formula or we have to it some other way?
Dear sir, i need your help, I have 2 diff sheets and i want to highlights data with new entry if data is matching? if some one knows what i want please help me. Thanks
I believe this is what you want ruclips.net/video/_MDOYvErfyg/видео.html
In addition to sum, i may wanna do a count. how do i achieve that?
Same exact thing, just remove the multiplication by prices.
Is there any way to use the SUMIF function from other spreadsheet without using IMPORTRANGE?
Spreadsheet - No
Worksheet - Yes
What if I want to have both Olivia and Grace by passing ["Olivia", "Grace"]?
It's here ruclips.net/video/cmtF5ulh6mo/видео.html
How to use sumif from diferent file ?
In your your tutorial, using sumif is fail
great explanation thanx a lot , can we add date condition in annother spredsheet, exp: sumproduct of element before a spécifice date?
or how we gonna do it if it's not work with sumproduct
thank you
Sure, you can do by date.
@@ExcelGoogleSheets
thenx a lot for ur responds
her is the equation
=(SUMPRODUCT(IMPORTRANGE(Q84,"all!$j$7:$j"),IMPORTRANGE(Q84,"all!$a$7:$a")30/7/2020 the result appear sum numbre before and after the date
please what is the issue
Remove > from the cell and change = to > in the formula condition
@@ExcelGoogleSheets
sorry but the same problem !
@@ExcelGoogleSheets please if there is annother function do this option
For me sumifs functoion works even when it's wrapped around importrange function. When I replace both arguments with import range. I don't know why it's working for me and it's not working for you... Great video nevertheless
Thank you
Maybe there was an update? Anybody else has this working with SUMIFS?
Can you do an updated Xpath video?
Nothing changed since the video. What's the point of the update?
@@ExcelGoogleSheetsFair enough. I just have a hard time following and getting it to work.
@@stevenpineiro-cdot4671 It won't work for every website.
Learn Google Spreadsheets Thank you.