Sum of a list of values if the vlookup of another column is 1

[scottiex]

I'm trying to find as concise a formula as possible to do this.

It needs to get the sum of a list of values from a column in a table if the "vlookup" of the corresponding cell in another column in that same table against another table is = 1.


in essence it is a bit like a "sum if" but it has a dynamic criteria...

I hope that is clear.

worksheet example attached. in this case it is the sum of A and B because only A and B are "on".


sumif.xlsx

[azumi]

Not sure but try this....

[scottiex]

A little more background - I want to be able to use VBA to enter this formula into a cell - therefore I want it to be small enough to write in a single formula (without using helper cells).

the second thing is that the criteria in column A and B are a list of criteria that it is acceptable to include in my sum from columns E/F. So if i repeat criteriaA, as i have at the bottom of the list, it should be included in the total that is the result i want.

Apologies if I have not done the best job of explaining this.

[scottiex]

Maybe this sheet is more clear - the result I want is the sum at the bottom.

User selects from the list (which in the end probably has about a dozen options) which of the rows qualify to be included in that sum.

sumif2.xlsx

The format of the table I am trying to add the sum to is quite locked down.

[samba_ravi]

C14=SUMPRODUCT(SUMIFS(C$4:C$13,$B$4:$B$13,$J$4:$J$6)*($K$4:$K$6>0))

Please Login or Register  to view this content.

Try this and copy towards right

[scottiex]

Thanks Siva, that exactly what I needed.

Reputation added !