Using Sumif for 2 criterias

[Jarod]

I have a sheet that I'm using to calculate counts and sums based on values
that change daily.

I have "Days" (A1:A10) column that tells me how long an item has been
sitting idle.
I have "UPB" (B1:B10) field that tells me the value of that item.

What I'm trying to do is get a sum if days is greater than or equal to 180
and the UPB is less than 50,000.

I need to put the results in A12, if that matters to you.

 

[Gary''s Student]

Something like:

=SUMPRODUCT((A1:A10>180)*(B1:B10<50000)*(B1:B10))


For example:

100 12
100 12
100 23
100 45
100 67
100 43
190 500000
190 1
190 2
190 3

returns 6

Whenever you have more than one criteria alwasy consider =SUMPRODUCT(). For
more detail see:

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

 

[Gary''s Student]

Something like:

=SUMPRODUCT((A1:A10>180)*(B1:B10<50000)*(B1:B10))


For example:

100 12
100 12
100 23
100 45
100 67
100 43
190 500000
190 1
190 2
190 3

returns 6

Whenever you have more than one criteria alwasy consider =SUMPRODUCT(). For
more detail see:

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

 

[Bernie Deitrick]

Jarod,

In A12, enter

=SUMPRODUCT((A1:A10>=180)*(B1:B10<50000))

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Jarod,

In A12, enter

=SUMPRODUCT((A1:A10>=180)*(B1:B10<50000))

HTH,
Bernie
MS Excel MVP

 

[Eduardo]

Hi,
=sumproduct(--(A1:A10=>180),--(B1:B10<50000),B1:B10)

 

[Eduardo]

Hi,
=sumproduct(--(A1:A10=>180),--(B1:B10<50000),B1:B10)

 

[Bernie Deitrick]

I'm sorry - my mind stopped on 'calculate counts'

=SUMPRODUCT((A1:A10>=180)*(B1:B10<50000))

For sums

=SUMPRODUCT((A1:A10>=180)*(B1:B10<50000)*B1:B10)

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

I'm sorry - my mind stopped on 'calculate counts'

=SUMPRODUCT((A1:A10>=180)*(B1:B10<50000))

For sums

=SUMPRODUCT((A1:A10>=180)*(B1:B10<50000)*B1:B10)

HTH,
Bernie
MS Excel MVP