Sign in to join the conversation:
Has anyone had success using the sumifs function as part of cross-sheet formulas?
I have been giving it a whirl and haven't had much luck. I am not sure if it is even possible, and if is then I'm not sure what I'm missing.
It is possible. Are you able to give more detail about your specific need?
Thanks Paul. I have an sheet set up to track payment income and related dates of payment et al.
Within that sheet I wanted to track income per week so I added some columns to the left of the sheet to roll-up the data I need, then have this data appear in a Dashboard/Sight. Now that the formulas work I hide the columns so they're out of sight.
I'd like to instead have the data on a separate roll-up sheet and keep things cleaner.
On my original sheet here is one of the formula examples:
=SUMIFS([Gross Invoice Amount]:[Gross Invoice Amount], [Date Payment Received]:[Date Payment Received], <=[Payment Week]3, [Date Payment Received]:[Date Payment Received], >=[Payment Week]2)
Attached is a snapshot of the columns & rows. Dates are added manually (for now).
Ideally I'd like to have these formulas/calculations on a separate roll-up sheet, so I can add add'l Parent/Child organization, sorting etc to my source sheet.
Let me know if this sheds enough light.
Ah. Ok. When you are building a formula, in the dropdown "help" box you will see a link that says "Reference Another Sheet". Simply click the link then select the appropriate sheet you want to pull the data from. Instead of having "[Gross Invoice Amount]:[Gross Invoice Amount]", it will look more like "{Sheet Name Range 1}". You can do this for each of your ranges whether it be the sum range or a criteria range.
Does that help, or do you need more detail?
Hi Paul - that worked finally - thank you for clarifying and helping out!
cheers,Chris
Excellent! Happy we could help.
Hi! I'm fairly new to SmartSheets but have been trying a number of things that havent yet worked. I want to be able to show for each parent row, the number of child rows completed as a %. So for the parent row Process Overview there are 7 tasks and 6 tasks are complete so I want it to show as 90%. Can anyone help me?
I have a column named Territory, where I am trying to write a formula to populate it when names are chosen from an adjacent drop-down list column called Employee Attendee. If more than one name is selected from the Employee column, I would like their respective territories to populate in the Territory field, separated by a…
Hello, I am trying to find the average audit score for a range of percentages between two dates. There are divider rows for organization by month. I have been trying to utilize the following formula but continue to get #DIVIDE BY ZERO for an answer: =AVG(COLLECT([Audit Score]:[Audit Score], [Date of Chat]:[Date of Chat],…
