Using Sumif for 2 criterias
 

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.

Using Sumif for 2 criterias
 

Something like:

=SUMPRODUCT((A1:A10180)*(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 - gsnu200852


"Jarod" wrote:

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.

Using Sumif for 2 criterias
 

Jarod,

In A12, enter

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

HTH,
Bernie
MS Excel MVP


"Jarod" wrote in message
...
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.

Using Sumif for 2 criterias
 

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

"Jarod" wrote:

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.

Using Sumif for 2 criterias
 

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" <deitbe @ consumer dot org wrote in message
...
Jarod,

In A12, enter

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

HTH,
Bernie
MS Excel MVP


"Jarod" wrote in message
...
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.