sumif formula
 

Hello, I made a formula that looks like this, =SUMIF(L:L,"HCAIN",P: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!
--
Louie

sumif formula
 

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"),--(P1:P1000),P1:P100)

Biff

"Louie" wrote in message
...
Hello, I made a formula that looks like this, =SUMIF(L:L,"HCAIN",P: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!
--
Louie

sumif formula
 

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
--
macropod
[MVP - Microsoft Word]
-------------------------

"Louie" wrote in message ...
Hello, I made a formula that looks like this, =SUMIF(L:L,"HCAIN",P: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!
--
Louie

sumif formula
 

thanks for your quick response, that worked perfect!

--
Louie


"T. Valko" wrote:

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"),--(P1:P1000),P1:P100)

Biff

"Louie" wrote in message
...
Hello, I made a formula that looks like this, =SUMIF(L:L,"HCAIN",P: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!
--
Louie

sumif formula
 

You're welcome. Thanks for the feedback!

Biff

"Louie" wrote in message
...
thanks for your quick response, that worked perfect!

--
Louie


"T. Valko" wrote:

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"),--(P1:P1000),P1:P100)

Biff

"Louie" wrote in message
...
Hello, I made a formula that looks like this, =SUMIF(L:L,"HCAIN",P: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!
--
Louie

sumif formula
 

XL2007

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


"Louie" wrote:

Hello, I made a formula that looks like this, =SUMIF(L:L,"HCAIN",P: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!
--
Louie