|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 14th, 2009, 06:31 PM
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
May 14th, 2009, 07:07 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using Sumif for 2 criterias
Something like:
=SUMPRODUCT((A1:A10180)*(B1:B1050000)*(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
May 14th, 2009, 07:09 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using Sumif for 2 criterias
Jarod,
In A12, enter =SUMPRODUCT((A1:A10=180)*(B1:B1050000)) 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
May 14th, 2009, 07:13 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using Sumif for 2 criterias
Hi,
=sumproduct(--(A1:A10=180),--(B1:B1050000),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
May 14th, 2009, 07:17 PM
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:B1050000)) For sums =SUMPRODUCT((A1:A10=180)*(B1:B1050000)*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:B1050000)) 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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
