CODEWITHSUNDEEP
excelvlookup
Kurt Klinke
Kurt Klinke

Reputation: 1

How to use a VLOOKUP as a criteria in SUMIF?

First allow me to provide some background:

I have two tables, the first with one column that has a key value, which is just one letter of the alphabet and a second column with values that later I want to sum. The second table also has two columns. The first contains a list of the distinct values from the key column of the first table, and the second contains a binary value of 1 or 0. Examples below:

Table 1:                 Table 2:
A   50                   A   1
A   50                   B   0
B   100                  C   0
C   125

The binary values in Table 2 are variable based on other information contained in my workbook. Now what I want to do is to sum all of the values in Table 1, where the Primary key is "turned on" in Table 2, aka the value in the second column is 1. I've tried multiple iterations of using SUMIF and VLOOKUP and LOOKUP as the criteria but can't get anything to work. Can anyone help out with a way, either using VBA or in the cell formula itself, to get this done?

Thanks in advance.

Upvotes: 0

Views: 859

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

How's this? Place this next to the 1 or 0 in your Table2, =IF(F1>0,SUMIFS($B$1:$B$4,$A$1:$A$4,E1),""):

enter image description here

and if you drag that down, it'll return blanks for the "letter" that doesn't have duplicates.

Edit: Or, per @pnuts, this =SUMIF(A:A,"="&INDEX(E:E,MATCH(1,F:F,0)),B:B)

Upvotes: 0

Related Questions