nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Welcome!

sumifs as Cross-sheet formulas

@chrismcgregor

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.

Find more posts tagged with

Construction

Formulas

Comments

Paul Newcome

It is possible. Are you able to give more detail about your specific need?

@chrismcgregor

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.

Income Tracker.JPG

Paul Newcome

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?

@chrismcgregor

Hi Paul - that worked finally - thank you for clarifying and helping out!

cheers,

Chris

Paul Newcome

Excellent! Happy we could help.

Sign in to join the conversation!

Quick Links

Trending discussions

MAX Formula Cells Updating in Reports
We have two cells with 'planned start' and 'planned end' dates. We also have two cells for 'actual/revised start date' and 'actual/revised end date'. These are designed to allow managers to enter the estimated start/end date of a milestone, and the revised start and end date when things slip. The actual/revised end date…
COUNTIFS - count the number of dates that fall within one month
Hi Community! Hoping you can assist. Can you tell me what is wrong with this formula for counting how many publications that fall within one month? =COUNTIFS( [Publication Date]:[Publication Date], >=EOMONTH(TODAY(), -1) + 1, [Publication Date]:[Publication Date], <=EOMONTH(TODAY(), 0) ) The date in the Publication Date…
Cross-Sheet Formula Help
I have two sheets: the first sheet is called 'Source Sheet', and the second is called 'Helper Sheet'. I want to use the Helper Sheet to track survey responses that are fed into the Source Sheet. The formula should reference a column in the first sheet named 'Company Email'. If that column is not blank on the Source Sheet,…