How to sum up values based on multiple criterions

This is sample data. I want to sum up original estimates (column circled with RED) by column EPIC# when:

1. non-epic helper ="Yes"

2. current jira status <> "Closed"

3. Jira# = Epic#.

So, for 1st four rows, I should have sum=24+60+40=128. Next, 3 rows since don't meet all the criteria should give sum=0. I tried couple ways, but since I can't get all values matching in vlookup, I can't sum them up. I did browse thru a few discussions but was unable to get this working. Any help is appreciated!

Find more posts tagged with

Accepted answers

All comments

Adam Murphy

Since you are using parent rows you can simply would put =SUM(CHILDREN()) where the 128 and the 0 are (on your parent rows), and then i'd put =IF(MID([Jira#]@row, 6, 5) = MID([Epic#]@row, 6, 5), [Original Estimate]@row, 0) in that same column on the child rows. This may not be the best method but would work. Note that I accounted for 5 digit Jira/Epic numbers, you can increase that if needed. Hope this helps!

PurnimaSenwal

I have a lot of data in my sheet. This seems more work to align based on parent child rows and then update as & when more data is added. Would there be any other way out if I remove parent child relation, does that make it easier?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.