Sum value based on multiple criteria including name and > and < date range

0
votes

I am trying to create a formula in Excel that automatically calculates the total value from a range based on a number of criteria across multiple sheets in a workbook. I am working in two different sheets:

Here is a link to view the workbook: https://docs.google.com/spreadsheets/d/1vf-wcaVokKr7apkoibM7Vx-q2MYJkmLlDl8wsKW8TVo/edit?usp=sharing

Sheet 1 contains all my input data:

  • Forecast $
  • Description
  • Start Date
  • End Date

Sheet 2 is the client facing report that contains:

  • Category
  • Date range (weekly breakdown of a financial year)
  • Total values

In the Total Values column of Sheet 2 I want to automatically sum:

Sheet 1 Forecast $ based on Sheet 1 Description = Sheet 2 Category but only for the date range where (Sheet 1 start date is (>= Sheet 2 beginning of week date range and <= Sheet 2 end of week date range)

The calculation needs to be applied across 4 different categories and 52 different start and end date weeks in Sheet 2.

Is this possible?

1
excelexcel-formula
Welcome to SO. Please provide same example how you're data looks like, not just a description. - RHA
Like @RHA says, a picture or example is endlessly useful in getting help. Trying to interpret your description, you might want to look at the 'SUMIFS' function (note the S) which lets you SUM based upon a number of conditions - Trum
thanks. Stack overflow requires an approval rating of 10 or higher to post images. Is there another way to do this? - user5362080

1 Answers

0
votes

My rating is not high enough to add this as a comment, but this site is good for creating a table for SO.

https://senseful.github.io/web-tools/text-table/