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" <SamFortMyers@discussions.microsoft.com> wrote in message
news:B19F1609-CD94-4085-86A0-51DE7C0EDC88@microsoft.com...
> 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" <SamFortMyers@discussions.microsoft.com> wrote in message
> news:B19F1609-CD94-4085-86A0-51DE7C0EDC88@microsoft.com...
> > 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:A100>0),--(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" <SamFortMyers@discussions.microsoft.com> wrote in message
news:84E51F79-16A1-4A6B-809F-99262D8B9312@microsoft.com...
> 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" <SamFortMyers@discussions.microsoft.com> wrote in message
>> news:B19F1609-CD94-4085-86A0-51DE7C0EDC88@microsoft.com...
>> > 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:A100>0),--(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" <SamFortMyers@discussions.microsoft.com> wrote in message
> news:84E51F79-16A1-4A6B-809F-99262D8B9312@microsoft.com...
> > 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" <SamFortMyers@discussions.microsoft.com> wrote in message
> >> news:B19F1609-CD94-4085-86A0-51DE7C0EDC88@microsoft.com...
> >> > 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.
> >>
> >>
> >>
>
>
>