# SUMIF but only +ve values

#### SHARPY1

##### Board Regular
I have this formula
=SUMIF('June Data'!\$C\$2:\$C\$2495,\$A1,'June Data'!E\$2:E\$2495)

However i only want to total values in column E which are +ve values i.e. >0

Cheers
Rich

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### SHARPY1

##### Board Regular
Slightly different this one mate,
this is a sumif other was a countif.

#### DonkeyOte

##### MrExcel MVP
hmmm...

for a sumif

=SUMPRODUCT(--('June Data'!\$C\$2:\$C\$2495=\$A1),--('June Data'!\$E\$2:\$E\$2495>0),--('June Data'!\$E\$2:\$E\$2495))

for a countif you would remove the final multiplication by value, so

=SUMPRODUCT(--('June Data'!\$C\$2:\$C\$2495=\$A1),--('June Data'!\$E\$2:\$E\$2495>0))

SUMPRODUCT basically will return 0 (False) or 1 (True) for every test for every cell in your test range...and multiply the results

ie

0*1 = 0
1*1 = 1
1*0 = 0

this is how get the count, to get the sumif you in turn multiply the above by the value of that row

0*1*10000 = 0
1*1*1250 = 1250
1*0*1250 = 0

hopefully that makes sense

Last edited:

Replies
3
Views
234
Replies
1
Views
154
Replies
5
Views
195
Replies
6
Views
246
Replies
19
Views
172

1,191,134
Messages
5,984,864
Members
439,921
Latest member
Neocold

### 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