Google Sheets: compare 2 column on different sheets and sum

Hi jeffereybrown,

A little back story: I am building out an app for the first time using the service Glide (https://www.glideapps.com).

It is my understanding that in order to have the formula =SUMIF(Timesheet!A:A,A2,Timesheet!D:D) automatically populate with a new data entry form the app, I need to have ARRAYFORMULA as part of the current SUMIF formula. Reading like this: =ARRAYFORMULA(SUMIF(Timesheet!A:A,A2,Timesheet!D:D)).

Sorry if this is not easy to follow.

If you look at https://docs.glideapps.com/all/refer...s/arrayformula GlideApp, explains why ARRAYFORMULA is needed.

Here is an except:

You’ve probably written a formula in the first row of your sheet and then copied it all the way down the column. However, this can cause issues:
When new rows get added – the formula doesn't get copied down. Glide can see this formula and can count it as an existing – even if you don't see data in your sheet. This results in new records that are added in your Glide app appearing at the bottom of your sheet Well =ARRAYFORMULA can help with that. The =ARRAYFORMULA can be used for lots of things but one of the simplest is copying.

I hope this helps you better understand the concept of what I am trying to do. In a nut shell, I am looking for the above formula to be copied to any new entry of data. That way I can track the total hours for each project, based on the timesheet clock in and out.

Thank you

Hi Ali,

Thank you for transferring this thread to the right forum. I was not aware that ARRAYFORMULA was only used in Google Sheets.

as mentioned by others, ArrayFormula() is just how google sheets wraps an array formula, in excel an array is wrapped in {curly brackets} by using CTRL + SHIFT + ENTER.

so if the formula works without it then there is no valid reason for adding it in "just for the hell of it"

In the simplest form here is what I am trying to achieve:

If the project name on either sheets in column A matches, sum the corresponding values from column D on sheet "timesheet" in column H on sheet "projects"

When a new row is added on sheet "project" I need the formula in column H to auto fill the new values based on the formula.

Hi joeartist,

See if the link below does what you want. There are 2 formulas in the blue cells on the Project sheet.

https://docs.google.com/spreadsheets...it?usp=sharing

Hi Flyboy65,

That did the trick, thank you for your help.

I am not sure what the =SORT(UNIQUE(Timesheet!A2:A))formula is for. When I put that into the google sheet I got a #REF! error. Not sure why, but the other formula worked perfectly.

Thank you

joeartist,

The SORT(UNIQUE(Timesheet!A2:A)) gives you a sorted unique list of the different Projects in Column A of the "Timesheet" sheet. If it isn't working for you, check that the range of cells (A2:A) is correct. If you still need help, perhaps you can post a link of your Google Sheet.