Sum rows if another list of rows has a specific value (lists are not equal size)

Question

I'll try to explain my question, couldn't find an answer anywhere because it's hard to formulate.

Lets say we have a list of costs and sub-costs (more detailed breakdown of the costs). Also, we have a list of only costs (it's shorter than the previous one, because contains only costs, without sub-costs). The second list has values TRUE or FALSE. I want to sum all costs from the first list that have TRUE value in the second list. SUMIF won't work because lists are not equal sized.

An example is on the picture. The sum must be 8 (because A and C are TRUE on the second list).

Answer 1

Try this array formula¹ in an appropriate cell.

=SUM(SUMIFS(B:B, A:A, IF(H1:H3, G1:G3)))

        

---

¹ _{Array formulas need to be finalized with Ctrl+Shift+Enter↵. If entered correctly, Excel with wrap the formula in braces (e.g. { and }). You do not type the braces in yourself. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.}

_{Sample in cloud}