Sum if row contains

ldavenportHAP

Hey All!

i am looking for help with a formula

i want to sum all "Actual Time Spent" in the top section "Monthly Expectation" based on the total entered below in "Record of tasks"

So in Monthly Expectations the "Client Requests" would show a total of .75 based on what was recorded below

Cleversheet

There's definitely a better way to structure what you're intending, which would be to separate the Monthly Expectations and the Record of Tasks into their own sheets. However, in the meantime within this sheet you might try something like the following in [Actual Time Spent]4 to test the concept (copy/paste should work unless you have rows not visible in your image):

=SUMIFS([Actual Time Spent]9:[Actual Time Spent]50, [Tasks]2:[Tasks]50, [Actual Time Spent]@row)

The "50" value above is because the range has to include no more than the number of rows already in the sheet. Unfortunately, that will vary as you continue to add Record of Tasks entries.

So ... I recommend you split the sheets, and massage the SUMIFS function in the Monthly Expectations sheet to pull (or use) the values from the entire [Tasks] and [Actual Time Spent] columns in the Record of Tasks sheet.

You'll need to use cross-sheet references as appropriate, so what I'm offering here is not a final ready-to-go solution but simply an indication of how you could approach the issue to achieve the intended result.

ldavenportHAP

Thank you for the great info @Cleversheet !

So I created separate sheets and added Sheet Summaries to my Record of Tasks

I then created a Sheet Summary Report based off of this information and was hoping to be able to link the totals in the report to the "Monthly Expectation" sheet but i dont appear to be able to link a cell from a report? is there a better way to be doing this?

Cleversheet

Reports only contain info that's on a sheet somewhere, so you'll need to find the sheet containing what you want and retrieve it from there. There are multiple ways of accomplishing that, depending on your needs and circumstances (eg, cell-link; INDEX/MATCH; Data Shuttle; Datamesh; ...).

Once you've found the solution, if this input was helpful could you mark your query as Answered?

Best wishes...