sumif formula

[Guest]

Hello, I made a formula that looks like this, =SUMIF(L:L,"HCAIN",P)
however, I need it to sum only the positive numbers and ignore the negative
numbers, I am missing something in it and I cant figure it out.
thank you for your help!

 

[T. Valko]

Try this (note: you can't use entire columns as range references in this
formula (unless you're using Excel 2007))

=SUMPRODUCT(--(L1:L100="HCAIN"),--(P1100>0),P1100)

Biff

 

[macropod]

Hi Louie,

Try the following array formula:
=SUM(IF((L1:OFFSET(L1,MATCH("*",L:L,-1)-1,)="HCAIN")*(P1:OFFSET(L1,MATCH("*",L:L,-1)-1,)>0),P1:OFFSET(L1,MATCH("*",L:L,-1)-1,),))
which you input with 'Ctrl-Shift-Enter', instead of just 'Enter'.

Cheers

 

[Guest]

thanks for your quick response, that worked perfect!

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

thanks for your quick response, that worked perfect!

 

[Guest]

XL2007

=SUMIFS(P,L:L,"HCAIN",P,">0")