Sum Multiple Rows Sharing A Common Index Value-CodePudding

I can't seem to find any examples for this exact Index-Match function problem on this site.

The green boxes have the value of "x". What I'd like to achieve is to find every instance of "x" in column G (this will be dragged for all columns onwards), and essentially look-up in Column D for it's hours equivalent and sum them in a one cell calculation for each week.

e.g. in this section of the spreadsheet, it will sum: 3.75 7.5 3.75 15 = 30 hours

I started with: =INDEX($D$3:$D$157,MATCH("x",G3:G157,0)) which returns the right answer, i.e. 3.75 hours, so I attempted to loop the function using the following:

=AGGREGATE(9,0,INDEX($D$3:$D$157,,MATCH("x",G3:G157,0)))

Though this returns an #REF! error message.

This is a link to a screenshot of the spreadsheet for reference. (I don't have the necessary reputation to post the image with the question).

Any help on this problem would be appreciated. I would prefer an in-cell function solution, as I'm not confident with VBA.

CodePudding user response：

G2: =SUMIF(G$3:G$157,"x",$D$3:$D$157)

and fill right as far as required.

CodePudding user response：

It would be easier if you could add an hidden column with a formula "=IF(G7="x";D7;0)" and calculate the respective hours based on the presence of "x" in column G. Then you can just simply get the total for this hidden column. It is more readable and easy to maintain.