CODEWITHSUNDEEP
excelcalculated-columns
Sunnyk
Sunnyk

Reputation: 21

calculating in excel based on criteria from different columns

I have been looking at different forums and I hope someone could help me here. I want to conditionally format a cell based on certain criteria The below would be a master table from which I will need to select data

and compute the values in this table

So, in the formula, I want to see how Phil's time is allocated across three Sprints. So, I will need to look at Sprint column in table 1 and search for Sprint 1, find Phil assigned to Sprint 1 and add the "estimate" total in table 2.

I saw something like this on the forum, but, no luck. I also tried using lookups and index and all I see are errors. If you have an easy way of solving for this, I would really appreciate.

Upvotes: 2

Views: 46

Answers (2)

user4039065
user4039065

Reputation:

This looks like a pretty basic sumifs.

=SUMIFS(Table1[estimate], Table1[sprint], H$2, Table1[assignee], $G3)

Don't drag the formula to fill the other cells. Copy and paste into the larger area. Dragging will shift the column references like relative cell addressing. Copying and pasting will treat the structured table references as absolute.

enter image description here

Upvotes: 0

girlvsdata
girlvsdata

Reputation: 1644

You can use SUMIFS

The formula in B2 is:

=SUMIFS($I:$I,$J:$J,$A2,$G:$G,B$1)

Sprint Screenshot

Upvotes: 1

Related Questions