Adding Similar Vlookup Values

[pvkvimalan]

Dear All,

I first thank the Admin & contributors of the forum for having provided a platform to learn & discuss.

This is my first post in the forum. I'm in need of a help.

I'm in process of making a Tracker for 10 Elements in order to complete 1 Activity. There are 100 such activities.
Under each Element, there are drop down list with 4 Status. (Not Started, Draft, Final Draft, Submitted)
Each Status has values assigned in a new sheet "Sheet1". (Not Started=0,Draft=2%, Final Draft=5%, Submitted=10%)
I would like to get the value of these elements added to form the results.

Right now I'm using Vlookup formula and it seems working fine. However, the length of the formula is getting large, as the number of elements being added

eg.C5 =(VLOOKUP(D5,Sheet1!$C$2:$D$5,2,FALSE))+(VLOOKUP(E5,Sheet1!$C$2:$D$5,2,FALSE))+(VLOOKUP(F5,Sheet1!!$C$2:$D$5,2,FALSE))...... And So on.

I would like to reduce the formula with any other function. Kindly provide valuable inputs.

The table in Sheet1 is as follows


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]#[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Not Started[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Draft[/TD]
[TD]2%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 112"]
<tbody>[TR]
[TD]Final Draft[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]5%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 112"]
<tbody>[TR]
[TD]Submitted[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10%[/TD]
[/TR]
</tbody>[/TABLE]

 

[AFPathfinder]

The typical solution for reducing a formula size would be to split the formula across additional cells, where you may have additional columns that are doing individual VLOOKUP functions and then you have one formula in C5 that is just adding those cells together.

Another option is to use a COUNTIFS function to count how many of each type is in the row, then multiply that by the VLOOKUP formula. it would still be a rather large formula, but may save you some space.

 

[pvkvimalan]

The typical solution for reducing a formula size would be to split the formula across additional cells, where you may have additional columns that are doing individual VLOOKUP functions and then you have one formula in C5 that is just adding those cells together.

Another option is to use a COUNTIFS function to count how many of each type is in the row, then multiply that by the VLOOKUP formula. it would still be a rather large formula, but may save you some space.

Dear AFPathfinder,

Thank you for your swift response. I used the 1st solution, the vlookup calculation is now placed in the background and it has considerably reduced the formula. However, I'm interested to try how the 2nd Solution works.

 

[AFPathfinder]

It would be something like this:

=COUNTIFS(B2:K2,"=Not Started")*VLOOKUP("Not Started",Sheet1!$C$2:$D$5,2,FALSE)
+COUNTIFS(B2:K2,"=Draft")*VLOOKUP("Draft",Sheet1!$C$2:$D$5,2,FALSE)
+COUNTIFS(B2:K2,"=Final Draft")*VLOOKUP("Final Draft",Sheet1!$C$2:$D$5,2,FALSE)
+COUNTIFS(B2:K2,"=Submitted")*VLOOKUP("Submitted",Sheet1!$C$2:$D$5,2,FALSE)

Also, in-cell carriage returns (Alt+Enter) do not hinder formulas, so you can help readability by layering functions (sometimes, anyway).

 

[pvkvimalan]

DEAR AFPathfinder

It worked like a charm. Thank you

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(SUMIFS(Sheet1!$D$2:$D$5,Sheet1!$C$2:$C$5,C5:F5))