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?

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?

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?

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?

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?

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?

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?