CODEWITHSUNDEEP
excelexcel-formulasumifs
Andy_NPT
Andy_NPT

Reputation: 37

Excel: Sumifs Formula, with possible Index Match?

I'm trying to create a SUMIFS that has the smarts to check a "child" row within a data range, then using the "parent" ID number (listed in the child row) checks the parents data and sums. I've inserted a picture of a table below which gives a dummy data set. The desired results of such a formula would be:

Child with parent in phase 1: $0

Child with parent in phase 2: $80

Child with parent in phase 3: $10

Thanks in advance for the assistance!

Example Data Set

Or as text:

ID  Type    Parent ID   Phase   Finances
1   Single              1       $10
2   Parent              2       $10
3   Child   2                   $10
4   Single              3       $10
5   Parent              3       $10
6   Child   5                   $10
7   Single              1       $10
8   Parent              2       $10
9   Child   8                   $10
10  Child   8                   $10
11  Child   8                   $10
12  Child   8                   $10
13  Child   8                   $10
14  Child   8                   $10
15  Child   8                   $10

Upvotes: 1

Views: 2582

Answers (2)

Scott Craner
Scott Craner

Reputation: 152450

Here it is in one array formula:

=SUM(IF($C$2:$C$16<>"",(LOOKUP($C$2:$C$16,$A$2:$A$16,$D$2:$D$16)=G2)*$E$2:$E$16))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter when leaving edit mode. If done correctly Excel will put {} around the formula.

enter image description here

Upvotes: 1

Forward Ed
Forward Ed

Reputation: 9874

Assuming your table is in A1 to E16.

Generate a helper column which fills in the blank phases. The blank phases will be filled in assuming that they have the same phase as the parent ID. Therefore in F1 place the following formula ans copy down to F16:

=IF(D2<>"",D2,VLOOKUP(C2,$A$2:$D$16,4,0))

Now we can use a sumproduct and take the total of only children and phase 2. In H1 to J3 I built I summary table of what you were looking for. In H1 to J1 I used headers and in H2 to I 4 I defined what I was searching for. In J2 to J4 I used the a formula for determine the finances as follows:

Type   |  Phase  |  Finance
Child  |    1    |  =SUMPRODUCT(($B$2:$B$16=H2)*($F$2:$F$16=I2)*$E$2:$E$16)
Child  |    2    |  =SUMPRODUCT(($B$2:$B$16=H3)*($F$2:$F$16=I3)*$E$2:$E$16) 
Child  |    3    |  =SUMPRODUCT(($B$2:$B$16=H4)*($F$2:$F$16=I4)*$E$2:$E$16)

The sumproduct only totals the rows from your table where there is CHILD (as defined in column H) and phase equal to the number defined in I, and only sums the values that correspond in your finance columns. If either child is not present in B or or the phase in F is not equal to what number is being searched than that row is valued as 0.

Proof of concept

Proof of Concept

Upvotes: 1

Related Questions