SUMIF formula based on a date range from anther sheet

MikeA

Hi,

I'm trying to create a summary sheet that gives me a summary of data from a master sheet. In the summary sheet I'm trying to first do a lookup on the master sheet for a date range of rows based on invoice date column. If the Invoice date is for the given week, then I would like to add the Amount column values for all of those rows and have that show in the summary sheet cell.

I hope that makes sense.

I'm new to SS and having trouble with how that would look. I think it's a combination of IF and VLOOKUP and even SUM.

MikeA

I have an update on the formula that I think will work, but I'm getting an #INVALID OPERATION error.

So the formula below is in the summary sheet and the only reference cells in the reference sheet are the start of the week date and the end of the week date. I need to reference those dates to lookup the rows to pull and add together from the master job sheet.

=SUMIFS({Invoiced Amount Range}, {Job Status Range}, OR({Job Status Range} = "Done", {Job Status Range} = "Invoiced"), {Job Invoice Date Range}, OR({Job Invoice Date Range} >= [Week Start Date]@row, {Job Invoice Date Range} <= [Week End Date]@row)

Happy to add any more detail if that helps.

Mike

Genevieve P.

Hi Mike,

Looks like you've almost got it! The only thing to adjust would be to use @cell as your range within the OR Functions - you're saying that in the previously stated column, this criteria should be within an individual cell. (Here's more information on the @cell function)

Try this:

=SUMIFS({Invoiced Amount Range}, {Job Status Range}, OR(@cell = "Done", @cell = "Invoiced"), {Job Invoice Date Range}, OR(@cell >= [Week Start Date]@row, @cell <= [Week End Date]@row))

Just a note about your ranges at the end... do you want OR or AND for the dates? With OR, if the week Start Date is less than what's in this sheet, but the Week End Date is also less, you will still receive a SUM. If you're looking between the range you may wantto use AND:

=SUMIFS({Invoiced Amount Range}, {Job Status Range}, OR(@cell = "Done", @cell = "Invoiced"), {Job Invoice Date Range},AND(@cell >= [Week Start Date]@row, @cell <= [Week End Date]@row))

Let me know if this works for you!

Cheers,

Genevieve