| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum of items in a column if they meet two criteria in another colu
I have a spreadsheet that lists in the A column how many days an order has
been open. In the B column I have how much money the order is for. I want to get a total dollar amount of invoices that have been open for 30 days or less, 31 to 60 days and over 61 days. Is this something I can do with one of the SUM functions or with SUBTOTAL? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of items in a column if they meet two criteria in another colu
30 days or less:
=SUMIF(A2:A100,"<=30",B2:B100) 31 to 60 days: =SUMIF(A2:A100,"=31",B2:B100)-=SUMIF(A2:A100,"=61",B2:B100) 61 days or mo =SUMIF(A2:A100,"=61",B2:B100) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "vlpckett" wrote: I have a spreadsheet that lists in the A column how many days an order has been open. In the B column I have how much money the order is for. I want to get a total dollar amount of invoices that have been open for 30 days or less, 31 to 60 days and over 61 days. Is this something I can do with one of the SUM functions or with SUBTOTAL? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of items in a column if they meet two criteria in another colu
HI Luke
A small Typo in the second formula =SUMIF(A2:A100,"=31",B2:B100)-=SUMIF(A2:A100,"<=60",B2:B100) HTH John "Luke M" wrote in message ... 30 days or less: =SUMIF(A2:A100,"<=30",B2:B100) 31 to 60 days: =SUMIF(A2:A100,"=31",B2:B100)-=SUMIF(A2:A100,"=61",B2:B100) 61 days or mo =SUMIF(A2:A100,"=61",B2:B100) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "vlpckett" wrote: I have a spreadsheet that lists in the A column how many days an order has been open. In the B column I have how much money the order is for. I want to get a total dollar amount of invoices that have been open for 30 days or less, 31 to 60 days and over 61 days. Is this something I can do with one of the SUM functions or with SUBTOTAL? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of items in a column if they meet two criteria in another colu
Hi,
In 2007 you can use =SUMIF(A2:A100,"<31",B2:B100) =SUMIFS(B2:B100,A2:A100,"30",A2:A100,"<61") =SUMIF(A2:A100,"60",B2:B100) (Actually, your stated question skips 61 days). In 2003: The same first and last ones but the middle one can be written =SUMPRODUCT((A2:A10030)*(A2:A100<61)*B2:B100) -- If this helps, please click the Yes button Cheers, Shane Devenshire "vlpckett" wrote: I have a spreadsheet that lists in the A column how many days an order has been open. In the B column I have how much money the order is for. I want to get a total dollar amount of invoices that have been open for 30 days or less, 31 to 60 days and over 61 days. Is this something I can do with one of the SUM functions or with SUBTOTAL? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of items in a column if they meet two criteria in another colu
30 days or less:
=SUMIF(A2:A25,"<=30",B2:B25) 31 to 60 days: =SUMPRODUCT((A2:A25=31)*(A2:A25<=60)*B2:B25) 61 days or mo =SUMIF(A2:A25,"=61",B2:B25) -- Laura Cook "vlpckett" wrote in message ... I have a spreadsheet that lists in the A column how many days an order has been open. In the B column I have how much money the order is for. I want to get a total dollar amount of invoices that have been open for 30 days or less, 31 to 60 days and over 61 days. Is this something I can do with one of the SUM functions or with SUBTOTAL? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of items in a column if they meet two criteria in another colu
Sorry I just notice the minus sign less then over 60
John "John" wrote in message ... HI Luke A small Typo in the second formula =SUMIF(A2:A100,"=31",B2:B100)-=SUMIF(A2:A100,"<=60",B2:B100) HTH John "Luke M" wrote in message ... 30 days or less: =SUMIF(A2:A100,"<=30",B2:B100) 31 to 60 days: =SUMIF(A2:A100,"=31",B2:B100)-=SUMIF(A2:A100,"=61",B2:B100) 61 days or mo =SUMIF(A2:A100,"=61",B2:B100) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "vlpckett" wrote: I have a spreadsheet that lists in the A column how many days an order has been open. In the B column I have how much money the order is for. I want to get a total dollar amount of invoices that have been open for 30 days or less, 31 to 60 days and over 61 days. Is this something I can do with one of the SUM functions or with SUBTOTAL? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum of items in a column if they meet two criteria in another colu
Hi,
You can also use pivot tables. After creating the pivot table, you can group days into bins. No formulas required. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "vlpckett" wrote in message ... I have a spreadsheet that lists in the A column how many days an order has been open. In the B column I have how much money the order is for. I want to get a total dollar amount of invoices that have been open for 30 days or less, 31 to 60 days and over 61 days. Is this something I can do with one of the SUM functions or with SUBTOTAL? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sum column 3 if columns 1 and 2 meet criteria | Excel Worksheet Functions | |||
| Subtotals of Items in A Column based on Spec. Values found in Colu | Excel Discussion (Misc queries) | |||
| How do I get the total number of items that meet 2 criteria in Exc | Excel Worksheet Functions | |||
| If 2 cols meet a criteria then sum the 3rd column | Excel Worksheet Functions | |||
| Sum the values of one column, only if they meet certain criteria . | Excel Worksheet Functions | |||
Linear Mode
