Hi All, I thought this would be simple, but now I am stumped! i am trying to add up 14 cells with varying info in them:

all cells are side by side i.e (H5:U5)

I basically need the formula to look at each cell and if it has ANY of the following:

7,7A,7R,3,3A,3R,11,11A,11R to give it a value of 8 and then sum it up.For example, if i had 6 different cells with 7,3A,11,7R3,11 showing in them, the formula should return to me: 48

If this isn't hard enough I have another one that needs the same type of formula except it need to return values of 6 or 8 depending on the criteria.

Same 14 cells, formula to look at each cell and if it has ANY of the following:

7,7A,3,3A,11,11A, to give it a value of 8 and then sum it up AND look for 730,730A,330,330A and return the value of 6 and sum it up.Any help would be appreciated, I am not a computer expert, but know my way around enough, this seems like it would be simple but alas, I cannot crack this one.

Thank you all!

You could create a lookup table, something like: A B 1) 7 8 2) 7A 8 3) 7R 8 4) 3 8 5) 3A 8 6) 3R 8 7) 11 8 8) 11A 8 9) 11R 8 10) 730 6 11) 730A 6 12) 330 6 13) 330A 6Then simply use a VLOOKUP() to get your numbers:

H I J K 5) 7 3R 11a 730 6) 8 8 8 6Put this formula in H6:

~~=VLOOKUP(H5,$A$1:$B$13,2)~~EDIT: Better formula

=IFERROR(VLOOKUP(H5,$A$1:$B$13,2,FALSE),0)

and drag to U6.Then just total them at the end in V6

MIKE

=(COUNTIF(H5:U5,7) + COUNTIF(H5:U5,"=7A") + COUNTIF(H5:U5,"=7R") +

COUNTIF(H5:U5,3) + COUNTIF(H5:U5,"=3A") + COUNTIF(H5:U5,11) +

COUNTIF(H5:U5,"=11A")) * 8

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

OMG thanks so much you two, much appreciated! DerbyDad03 your solution was simple and now I understand how to modify it with the other info...

Thanks again :)

Actually, while my solution works, Mike's might be better if you ever need to change the data strings that you are counting or the values assigned.With Mike's solution, you only need to change the table. With my solution, you have to edit the admittedly cumbersome formula.

If it were me, I'd probably use Mike's method.

Perhaps Mike would like to explain how his method works so that you can use - and modify - it to fit you needs.

VLOOKUP is very powerful and can be used in many other situations.

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History