#### clares

##### Well-known Member
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

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
sum(range)+(10*countblank(range))

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)

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

clares said:
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))

Hi There

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

Kindest Regards

Peter

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

Thank you

Regards

Peter

clares said:
Hi There

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

Kindest Regards

Peter
Book5
ABCDEFGHIJKLMNOPQRSTUV
4
566596X600468830
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?

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?

Replies
10
Views
239
Replies
2
Views
124
Replies
6
Views
232
Replies
0
Views
219
Replies
6
Views
146

1,196,263
Messages
6,014,311
Members
441,814
Latest member
youngstubbs

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back