Sumif function with two criteria from different columns
 

Can I add a criterion to a SUMIF function?
After checking the initial range for a value, I want to make sure that the
cell in the Sum_range is 0 before completing the SUM.

IF(SUMIF(...)0,SUMIF(...),"Not valid")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SamFortMyers" wrote in message
...
Can I add a criterion to a SUMIF function?
After checking the initial range for a value, I want to make sure that the
cell in the Sum_range is 0 before completing the SUM.

This appears to test that the SUM is 0. I wanted to test each component cell
in the SUM_Range, ignoring any that showed a negative figure.

"Bob Phillips" wrote:

IF(SUMIF(...)0,SUMIF(...),"Not valid")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SamFortMyers" wrote in message
...
Can I add a criterion to a SUMIF function?
After checking the initial range for a value, I want to make sure that the
cell in the Sum_range is 0 before completing the SUM.

Hi

how about
=SUMPRODUCT(--(A1:A1000),--(B1:B100="blue"),A1:A100)

where A1:A100 is the range you want to SUM
B1:B100 is the range containing your criteria
and
blue is the criteria

check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

for details on using SUMPRODUCT

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"SamFortMyers" wrote in message
...
This appears to test that the SUM is 0. I wanted to test each component
cell
in the SUM_Range, ignoring any that showed a negative figure.

"Bob Phillips" wrote:

IF(SUMIF(...)0,SUMIF(...),"Not valid")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SamFortMyers" wrote in message
...
Can I add a criterion to a SUMIF function?
After checking the initial range for a value, I want to make sure that
the
cell in the Sum_range is 0 before completing the SUM.

Yes, that's the solution. I found it earlier today checking out that link to
xldynamic.com that Bob Phillips had referred to in a previous thread.

"JulieD" wrote:

Hi

how about
=SUMPRODUCT(--(A1:A1000),--(B1:B100="blue"),A1:A100)

where A1:A100 is the range you want to SUM
B1:B100 is the range containing your criteria
and
blue is the criteria

check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

for details on using SUMPRODUCT

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"SamFortMyers" wrote in message
...
This appears to test that the SUM is 0. I wanted to test each component
cell
in the SUM_Range, ignoring any that showed a negative figure.

"Bob Phillips" wrote:

IF(SUMIF(...)0,SUMIF(...),"Not valid")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SamFortMyers" wrote in message
...
Can I add a criterion to a SUMIF function?
After checking the initial range for a value, I want to make sure that
the
cell in the Sum_range is 0 before completing the SUM.