Sum using 2nd table as reference

[gaijinko]

Hello,

I cannot get my head to figure out the right formula to find a total for "Sum of Yes" in Table 1 in the example below. The sum of yes for DC (row 2) should be 4500.00 since column Bob and Column Ted for DC are yes, Florida is 5702.00, etc.

Thanks for any suggestions!


Table 1
State B C D E sum of YES
DC 4,000.00 480.00 500.00 202.00
Florida 5,000.00 1,260.00 500.00 202.00
Georgia 6,000.00 480.00 500.00 202.00
Hawaii 8,160.00 400.00 500.00 202.00
Idaho 7,000.00 660.00 500.00 202.00
Illinois 9,000.00 1,200.00 500.00 202.00
Indiana 1,000.00 480.00 500.00 202.00
Iowa 2,000.00 1,200.00 500.00 202.00

Table 2
State B C D E
DC YES NO YES NO
Florida YES NO YES YES
Georgia YES YES NO NO
Hawaii YES YES YES YES
Idaho YES NO NO NO
Illinois YES NO NO NO
Indiana YES YES NO NO
Iowa YES NO YES YES

 

[mrshl9898]

Not sure where the tables are.

In my Picture here https://ibb.co/b6hsoG

Formula in F2 - =SUM(IF(I2="YES",B2,0),IF(J2="YES",C2,0),IF(K2="YES",D2,0),IF(L2="YES",E2,0))

 

[barry houdini]

=SUM(IF(I2="YES",B2,0),IF(J2="YES",C2,0),IF(K2="YES",D2,0),IF(L2="YES",E2,0))

You could get the same result with SUMIF, i.e.

=SUMIF(I2:L2,"Yes",B2:E2)

 

[gaijinko]

Thanks. I realized that the formatting of my post didn't save and I had to run to a meeting so I appreciate that you got it.

Follow up question if I can....

What if the states in the first table were random or duplicated i.e.
Indiana
Indiana
Indiana
Georgia
DC
Florida
Idaho
Florida

Is there a sort of vlookup that can be added to the sumif formula?

 

[barry houdini]

You can use INDEX/MATCH within the SUMIF formula, e.g. assuming second table in J2:N10

=SUMIF(INDEX(K$2:N$10,MATCH(A2,J$2:J$10,0),0),"Yes",B2:E2)

 

[gaijinko]

Perfect. I was messing up index and match...thank you!