Sum of 14 numbers cannot figure out!

May 3, 2011 at 18:54:59
Specs: Windows Vista
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!


See More: Sum of 14 numbers cannot figure out!

Report •


#1
May 3, 2011 at 19:19:47
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     6

Then simply use a VLOOKUP() to get your numbers:

    H      I      J       K
 5) 7     3R     11a     730
 6) 8      8      8       6

Put 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

http://www.skeptic.com/


Report •

#2
May 5, 2011 at 20:31:09
=(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.


Report •

#3
May 5, 2011 at 22:50:06
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 :)


Report •

Related Solutions

#4
May 6, 2011 at 05:10:12
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.


Report •


Ask Question