best way to sum or count based on certain information

  • Thread starter Thread starter pb1
  • Start date Start date
P

pb1

If I have cells that have codes that have certain values what is th
best method of summing or counting those values?

For example:

A B C TOTAL
1 V U 1.5

2 I 0.0

3 V 1.0

Where
V = 1
U = 0.5
I = 0.0
2U = 1.0

I need to be able to make the totals based on the code. Any ideas?

Thanks so much.

-
 
pb1,

The easiest way is to make a copy of your sheet and use Replace (Ctrl-H) to
change all V's to 1's etc. then use simple sum formulas.

HTH,
Bernie
MS Excel MVP
 
I actually figured it out using the Help feature of Excel.

The formula is as follows:

=SUM(IF(E11:AI11="V",1,IF(E11:AI11="U",0.5,IF(E11:AI11="2U",1,0))))

Originally, I was receiving a #VALUE! error, but after selecting th
cell with the formula, pressing F2, and then CTRL+SHIFT+ENTER, creatin
an array formula, I received the desired results.

Thanks

-
 
Back
Top