Sumif Help please

[clares]

Hi All

I want to do a sumif on the following range but struggling:

C5,E5,G5,I5,K5,M5,O5,Q5,S5,U5,V5

The bit thats stumping me is i need to sum any empty/null cells in that range as a 10?

Can this be done using a sunif?

Kind Regards

Peter

 

[PaddyD]

sum(range)+(10*countblank(range))

 

[Aladin Akyurek]

If the assumption is right that every 2nd cell in C5:U5 is of interest along with V5, then try:

=SUMPRODUCT(--(MOD(COLUMN(C5:U5)-COLUMN(C5)+0,2)=0),C5:U5+(C5:U5=0)*10)+IF(V5,V5,10)

 

[clares]

Hey Guys

Thanks for that.

I have tried to use the SUMPRODUCT formula and get an error.I have tried to look at the help file:

Remarks

The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.

Not sure what i am doing wrong but I get the #Value! error?

Any ideas

Kind Regards

Peter

 

[Aladin Akyurek]

Hey Guys

Thanks for that.

I have tried to use the SUMPRODUCT formula and get an error.I have tried to look at the help file:

Remarks

The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.

Not sure what i am doing wrong but I get the #Value! error?

Any ideas

Kind Regards

Peter

I should have known that outcome...

Try:

=SUM(SUMPRODUCT(--(MOD(COLUMN(C5:U5)-COLUMN(C5)+0,2)=0),C5:U5),SUMPRODUCT(--(MOD(COLUMN(C5:U5)-COLUMN(C5)+0,2)=0),(C5:U5=0)*10),IF(V5,V5,0))

 

[clares]

Hi There

Sorry tried to new one,still getting the error. Am I doing something wrong?

Kindest Regards

Peter

 

[clares]

Sorry, now working ok. Thank you very much for your help.

Thank you

Regards

Peter

 

[Aladin Akyurek]

Hi There

Sorry tried to new one,still getting the error. Am I doing something wrong?

Kindest Regards

Peter

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
4
5	66				5	9	6	X	6	0	0		4		6		8		8		3	0
6
7
Sheet1

A5:

=SUM(SUMPRODUCT(--(MOD(COLUMN(C5:U5)-COLUMN(C5)+0,2)=0),C5:U5),SUMPRODUCT(--(MOD(COLUMN(C5:U5)-COLUMN(C5)+0,2)=0),(C5:U5=0)*10),IF(V5,V5,0))

Do you have error values in C5:V5?

 

[Peter_SSs]

clares

i need to sum any empty/null cells in that range as a 10?

Can you also just confirm what should happen if the cell has a 0 in it? Do you class this as 'empty/null' and add 10 or leave it as 0?