SUMIF but only +ve values

[SHARPY1]

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

 

[DonkeyOte]

Rich, please keep to original thread... the solution was in the last post I think, no ?

http://www.mrexcel.com/forum/showthread.php?t=328391

Thanks,
Luke

 

[SHARPY1]

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

 

[DonkeyOte]

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