Need advice on my sumif formula (text ref.)

D

delerious

Joined
Apr 7, 2011
Messages
2
Reaction score
0
Hi Everyone,

Just joined the forum, as hoping you knowledgable people will be able to advise me.

I'm an relatively experienced excel user, however fairly new to formulas....well anything other than basic.

Basically I am trying to sum a range of cells (X7:AB7), but within those cells will be one of 2 words...VAC (which I need to assign a numeric value of 1), and 0.5 VAC (which I need to assign a numeric value of 0.5).

Formula I have that doesn't work: =SUM(IF(X7:AB7="VAC",1,IF(X7:AB7="0.5 VAC",0.5,))) - this gives a #VALUE.

I've tried to find out a formula to do this, by searching the internet, and I suspect maybe a sumproduct type (or a count if), possibly nested, but I've got to the point where I am just going round in circles now, and not getting anywhere fast.

Thanks.
 
Just joined to help you since this forum helped me a minute ago. You have three methods:
1. type exactly what you had, but instead of pressing ENTER, press CTRL+SHIFT+ENTER (will calculate as an array).
2. =SUMPRODUCT((X7:AB7="VAC")*1)+SUMPRODUCT((X7:AB7="0.5 VAC")*0.5)
3. =COUNTIF(X7:AB7, "VAC")+COUNTIF(X7:AB7, "0.5 VAC")*0.5

So, good suspection. Hope it helps.

delerious said:
Hi Everyone,

Just joined the forum, as hoping you knowledgable people will be able to advise me.

I'm an relatively experienced excel user, however fairly new to formulas....well anything other than basic.

Basically I am trying to sum a range of cells (X7:AB7), but within those cells will be one of 2 words...VAC (which I need to assign a numeric value of 1), and 0.5 VAC (which I need to assign a numeric value of 0.5).

Formula I have that doesn't work: =SUM(IF(X7:AB7="VAC",1,IF(X7:AB7="0.5 VAC",0.5,))) - this gives a #VALUE.

I've tried to find out a formula to do this, by searching the internet, and I suspect maybe a sumproduct type (or a count if), possibly nested, but I've got to the point where I am just going round in circles now, and not getting anywhere fast.

Thanks.
Click to expand...
 
Last edited:
Thriller...thank you so much for responding...all of those worked great. :thumb:

Am also quietly impressed with myself, for getting so close :D
 
Back
Top