Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP and sums
Any help greatfully received !!! If I have a list of data as below ( eg )
1/1/08 10 8/1/08 15 15/1/08 20 22/1/08 30 29/1/08 40 The list has 'dates' in column A and numbers in column B and will always be in this format. How do I creata a formula that can add up column B total, but for a certain group of dates - ie the total of B for dates between and including 8/1/08 and 22/1/08. Obvioulsy I can expand the range, but thought this the easiest example of what I am trying to achieve !! Thanking anyone in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP and sums
Gilly,
There are a couple of ways and here's one =SUMIF(A1:A10,"="&C1,B1:B10)-SUMIF(A1:A10,"="&D1,B1:B10) Where C1 is the start date and D1 is the end date of your sum range Mike "Gilly B" wrote: Any help greatfully received !!! If I have a list of data as below ( eg ) 1/1/08 10 8/1/08 15 15/1/08 20 22/1/08 30 29/1/08 40 The list has 'dates' in column A and numbers in column B and will always be in this format. How do I creata a formula that can add up column B total, but for a certain group of dates - ie the total of B for dates between and including 8/1/08 and 22/1/08. Obvioulsy I can expand the range, but thought this the easiest example of what I am trying to achieve !! Thanking anyone in advance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP and sums
Hi,
Forgot to mention that Excel may try and be 'helpful' and format this formula as a date. If it does reformat as general. Mike "Mike H" wrote: Gilly, There are a couple of ways and here's one =SUMIF(A1:A10,"="&C1,B1:B10)-SUMIF(A1:A10,"="&D1,B1:B10) Where C1 is the start date and D1 is the end date of your sum range Mike "Gilly B" wrote: Any help greatfully received !!! If I have a list of data as below ( eg ) 1/1/08 10 8/1/08 15 15/1/08 20 22/1/08 30 29/1/08 40 The list has 'dates' in column A and numbers in column B and will always be in this format. How do I creata a formula that can add up column B total, but for a certain group of dates - ie the total of B for dates between and including 8/1/08 and 22/1/08. Obvioulsy I can expand the range, but thought this the easiest example of what I am trying to achieve !! Thanking anyone in advance. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP and sums
Here's a sumproduct version.
=SUMPRODUCT((A1:A10=C1)*(A1:A10<=D1)*(B1:B10)) Mike "Mike H" wrote: Hi, Forgot to mention that Excel may try and be 'helpful' and format this formula as a date. If it does reformat as general. Mike "Mike H" wrote: Gilly, There are a couple of ways and here's one =SUMIF(A1:A10,"="&C1,B1:B10)-SUMIF(A1:A10,"="&D1,B1:B10) Where C1 is the start date and D1 is the end date of your sum range Mike "Gilly B" wrote: Any help greatfully received !!! If I have a list of data as below ( eg ) 1/1/08 10 8/1/08 15 15/1/08 20 22/1/08 30 29/1/08 40 The list has 'dates' in column A and numbers in column B and will always be in this format. How do I creata a formula that can add up column B total, but for a certain group of dates - ie the total of B for dates between and including 8/1/08 and 22/1/08. Obvioulsy I can expand the range, but thought this the easiest example of what I am trying to achieve !! Thanking anyone in advance. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP and sums
for dates between and including 8/1/08 and 22/1/08.
=SUMIF(A1:A10,"="&C1,B1:B10)-SUMIF(A1:A10,"="&D1,B1:B10) To include the boundary dates the 2nd SUMIF criteria should be ""&D1. -- Biff Microsoft Excel MVP "Mike H" wrote in message ... Gilly, There are a couple of ways and here's one =SUMIF(A1:A10,"="&C1,B1:B10)-SUMIF(A1:A10,"="&D1,B1:B10) Where C1 is the start date and D1 is the end date of your sum range Mike "Gilly B" wrote: Any help greatfully received !!! If I have a list of data as below ( eg ) 1/1/08 10 8/1/08 15 15/1/08 20 22/1/08 30 29/1/08 40 The list has 'dates' in column A and numbers in column B and will always be in this format. How do I creata a formula that can add up column B total, but for a certain group of dates - ie the total of B for dates between and including 8/1/08 and 22/1/08. Obvioulsy I can expand the range, but thought this the easiest example of what I am trying to achieve !! Thanking anyone in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sums | Excel Worksheet Functions | |||
SUMS | Excel Discussion (Misc queries) | |||
How to add previous sums in a column to current sums in a column? | Excel Worksheet Functions | |||
Sums | Excel Discussion (Misc queries) | |||
Sums | Excel Discussion (Misc queries) |