Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif function with two criterias | Excel Worksheet Functions | |||
SUMIF with 2 criterias | Excel Discussion (Misc queries) | |||
Sumif with two criterias | Excel Worksheet Functions | |||
Sumif with two criterias | Excel Worksheet Functions | |||
SUMIF with 2 criterias | Excel Worksheet Functions |