Urgently need help with a formula!

Melissa Boehl

I have a sheet used to maintain a list of projects, I will call it "Master Project List" and I have a second sheet that the team uses for scheduling which I will call "Scheduler". Team members use a form to enter their planned hours on a project into the Scheduler which includes the project number and the task name. Each project will have multiple rows on the scheduler sometimes the same task is also listed several times. I need to keep track of their billing against the actual. I need a cross-sheet formula that I can use on the "Master Project List" that will find all the projects with a specified project number and task then sum the billed for those rows. Each row on the master project list has several columns for budget due to each task having a set budget. Therefore, I need to search the scheduler sheet from the master project list for a match of the [project number]@row and the task@row then sum all those rows. I have attempted using sumifs but that didn't produce any result. I have tried a combination of index, match, collect, if/and, etc... For some reason I am unable to put something together. Maybe its because its friday... lol. If you can help, I would really appreciate it.

Genevieve P.

Hi @Melissa Boehl

I agree that screen captures would help, but please block out sensitive data.

It does sound like a SUMIFS function would be the way to go, something like this:

=SUMIFS({Column to SUM}, {Project Number Column}, [Project Number]@row, {Task Column}, Task@row)

If this is returning 0 instead of a number, it could be because the column you're looking to SUM isn't recognized as housing numerical data. Check to see if the numbers in that column are on the LEFT side of the cell (meaning they're seen as Text), or on the RIGHT of the cell (which would indicate they're seen as numerical).

If the numbers are on the LEFT, are you using a formula to create these numbers in your source sheet? If so, what is that formula?

Cheers,

Genevieve

Summer

Hi @Melissa Boehl

It's not always easy to visualize the scenario from text. Would it be possible for you to remove any confidential information and share screenshots of the two sheets you are referring to?

This may help the community better understand what you are trying to achieve. Once screenshots are shared, don't forget to mark that my response doesn't answer your question. That way the community will know your ask is still outstanding.

Kindly,

Summer

Genevieve P.

Hi @Melissa Boehl

I agree that screen captures would help, but please block out sensitive data.

It does sound like a SUMIFS function would be the way to go, something like this:

=SUMIFS({Column to SUM}, {Project Number Column}, [Project Number]@row, {Task Column}, Task@row)

If this is returning 0 instead of a number, it could be because the column you're looking to SUM isn't recognized as housing numerical data. Check to see if the numbers in that column are on the LEFT side of the cell (meaning they're seen as Text), or on the RIGHT of the cell (which would indicate they're seen as numerical).

If the numbers are on the LEFT, are you using a formula to create these numbers in your source sheet? If so, what is that formula?

Cheers,

Genevieve

Melissa Boehl

@Genevieve P. I was able to figure this out. Sorry for the delay, I was away on leave for a while. Thank you for your help!

Genevieve P.

I'm glad you were able to figure this out! Thanks for following up 🙂