SUMPRODUCT, SUM IF from Other Spreadsheets (files) - Google Sheets

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

[@kennethforbin5604]

This guy is so awesome! You make my day with anything Google Query or Userform.

[@aviadbenhamo5812]

The best channel of Google SHEET!

[@user-un6hx2wq6j]

Great workaround! It will help me a lot! Have always avoided Sumifs

[@Puner54]

Teacher...it blows my mind what one can do with a modern spreadsheet! PS...I see your Auto Fill is working 👍

[@bruno_veloso]

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.

[@tonyrios1487]

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)

[@sidharthbajaj9237]

Great work again as always !!

[@sbhanuprakash9]

Thank you. Really it helps a lot.

[@hrushimehta]

Your videos are good. Google must pay you for publishing such power-packed videos. Keep it going. 👍👍

[@waqarnadeem4628]

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

[@lluiszardoyaVLOGS]

Dude you are a genius! This is so helpful!!!!!!!!!!

[@tianurhawa5043]

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!

[@PrashanthRuthala]

Excellent class!

[@papaduck3649]

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!

[@imamsibromuhlisi1520]

Awesome... This is helpfull! 👍

[@user-hf4pe4ul3d]

Thank you so much 🥰

[@Balanced69]

AMAZING VIDEO

[@prajwalshetty2047]

Thanks a lot Sir ❤🙏🇮🇳

[@rahuldev8282]

Thank you so much

[@Aquivoy]

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!

[@stefanarsic5828]

THANK YOU SIR!

[@krishnatate4765]

Very nice sir ❤

[@AFSALKND]

Very Helpful Videos Thank You Sir,

[@ExcelGoogleSheets]

👍

[@ubaidillahmuhammad20]

Nice and good job. Put practiced file in the description video ....

[@MrPlatipusbiru]

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?

[@viktornilsson3088]

Question! What if I don't want to search for a specific name, but include all names that start with O?

[@Ida83260]

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!

[@gavn79]

Hi, Is possible sum multiples columns with one conditions ?

[@gavn79]

Is posible sum several columns with sumproduct? But numbers no text

[@alfred4264]

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.

[@ExcelGoogleSheets]

No.

[@alfred4264]

🙁

[@baseballpeanut09]

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?

[@ExcelGoogleSheets]

Probably possible with sumproduct and arrays but it all depends on how the end result should function & how many rows of data you have.

[@baseballpeanut09]

@@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?

[@sadashivb5100]

Pls make video ..how to modified import XML data ...column or match with rows name.

[@stefanarsic5828]

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?

[@abhishekshrivastav2141]

Hi all, Any have idea how will be use sumproduct function in data studio. If any alternative suggestion for this.

[@cradleofrelaxation6473]

In addition to sum, i may wanna do a count. how do i achieve that?

[@ExcelGoogleSheets]

Same exact thing, just remove the multiplication by prices.

[@waqarahmad1651]

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

[@ExcelGoogleSheets]

I believe this is what you want kzbin.info/www/bejne/lX6ngIyseteZr8k

[@Leo-ep3bt]

Is there any way to use the SUMIF function from other spreadsheet without using IMPORTRANGE?

[@ExcelGoogleSheets]

Spreadsheet - No Worksheet - Yes

[@ousamamos8514]

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

[@ExcelGoogleSheets]

Sure, you can do by date.

[@ousamamos8514]

@@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

[@ExcelGoogleSheets]

Remove > from the cell and change = to > in the formula condition

[@ousamamos8514]

@@ExcelGoogleSheets sorry but the same problem !

[@ousamamos8514]

@@ExcelGoogleSheets please if there is annother function do this option

[@migueldias5052]

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

[@xdriyad]

What if I want to have both Olivia and Grace by passing ["Olivia", "Grace"]?

[@ExcelGoogleSheets]

It's here kzbin.info/www/bejne/mZ7Xd2iroc1po9E

[@stevenpineiro-cdot4671]

Can you do an updated Xpath video?

[@ExcelGoogleSheets]

Nothing changed since the video. What's the point of the update?

[@stevenpineiro-cdot4671]

@@ExcelGoogleSheetsFair enough. I just have a hard time following and getting it to work.

[@ExcelGoogleSheets]

@@stevenpineiro-cdot4671 It won't work for every website.

[@stevenpineiro-cdot4671]

Learn Google Spreadsheets Thank you.

[@romandeity4754]

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

[@ExcelGoogleSheets]

Maybe there was an update? Anybody else has this working with SUMIFS?

[@cupangpemberani1191]

How to use sumif from diferent file ? In your your tutorial, using sumif is fail