Need formula based on criteria.

I have a spreadsheet listed by day as follows.  I need the 
data from column B, C & D summarized by month. How do I do 
that?

Col A      Col B   Col C  Col D
01/01/04     5       5     4
02/01/04     6       6     6
03/03/04     7       0     0

I need the result to look like this -
Jan 04      11      11    10
Mar 04       7      0     0

How do I tell it to search for dates between 01/01/04 - 
31/01/04 in column A then add column B, C or D to comue up 
with my totals for Jan 04?
0
anonymous (74721)
2/5/2005 5:19:10 PM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
231 Views

Similar Articles

[PageSpeed] 50

Hi

one way

for Jan Col B use the following formula
=SUMPRODUCT(--(MONTH($A$2:$A$4)=1),B$2:B$4)

this can then be filled across for col C & D
for Feb
use
=SUMPRODUCT(--(MONTH($A$2:$A$4)=2),B$2:B$4)

Etc

alternatively, check out pivot tables - Debra Dalgleish has some good 
instructions on her website at www.contextures.com/tiptech.html

Cheers
JulieD


"Me" <anonymous@discussions.microsoft.com> wrote in message 
news:0b8f01c50ba6$ce521650$a501280a@phx.gbl...
>I have a spreadsheet listed by day as follows.  I need the
> data from column B, C & D summarized by month. How do I do
> that?
>
> Col A      Col B   Col C  Col D
> 01/01/04     5       5     4
> 02/01/04     6       6     6
> 03/03/04     7       0     0
>
> I need the result to look like this -
> Jan 04      11      11    10
> Mar 04       7      0     0
>
> How do I tell it to search for dates between 01/01/04 -
> 31/01/04 in column A then add column B, C or D to comue up
> with my totals for Jan 04? 


0
JulieD1 (2295)
2/5/2005 5:26:21 PM
Thank you.  But I forgot to mention that my data covers 
more than 1 year.  How do I incorporate the year into my 
formula, i.e. if I need to calculate Jan 2003 and also Jan 
2004 in another total?
>-----Original Message-----
>Hi
>
>one way
>
>for Jan Col B use the following formula
>=SUMPRODUCT(--(MONTH($A$2:$A$4)=1),B$2:B$4)
>
>this can then be filled across for col C & D
>for Feb
>use
>=SUMPRODUCT(--(MONTH($A$2:$A$4)=2),B$2:B$4)
>
>Etc
>
>alternatively, check out pivot tables - Debra Dalgleish 
has some good 
>instructions on her website at 
www.contextures.com/tiptech.html
>
>Cheers
>JulieD
>
>
>"Me" <anonymous@discussions.microsoft.com> wrote in 
message 
>news:0b8f01c50ba6$ce521650$a501280a@phx.gbl...
>>I have a spreadsheet listed by day as follows.  I need 
the
>> data from column B, C & D summarized by month. How do I 
do
>> that?
>>
>> Col A      Col B   Col C  Col D
>> 01/01/04     5       5     4
>> 02/01/04     6       6     6
>> 03/03/04     7       0     0
>>
>> I need the result to look like this -
>> Jan 04      11      11    10
>> Mar 04       7      0     0
>>
>> How do I tell it to search for dates between 01/01/04 -
>> 31/01/04 in column A then add column B, C or D to comue 
up
>> with my totals for Jan 04? 
>
>
>.
>
0
anonymous (74721)
2/5/2005 5:46:25 PM
Me wrote:
> I have a spreadsheet listed by day as follows.  I need the 
> data from column B, C & D summarized by month. How do I do 
> that?
> 
> Col A      Col B   Col C  Col D
> 01/01/04     5       5     4
> 02/01/04     6       6     6
> 03/03/04     7       0     0
> 
> I need the result to look like this -
> Jan 04      11      11    10
> Mar 04       7      0     0
> 
> How do I tell it to search for dates between 01/01/04 - 
> 31/01/04 in column A then add column B, C or D to comue up 
> with my totals for Jan 04?
If you format the date column to the form Jan-04 then you can do 
Data-Subtotals on each change in the month, or use Autofilter.

-- 
Registered Linux User no 240308
Just waiting for Broadband to complete the conversion!(3 weeks and 
counting!)
gordonATgbpcomputingDOTcoDOTuk
to email me remove the obvious!
0
gordonbp11 (453)
2/5/2005 5:47:09 PM
Hi

the formula can be edited to include year, e.g.

=SUMPRODUCT(--(MONTH($A$2:$A$4)=1),--(YEAR($A$2:$A$4)=2004),B$2:B$4)

but i would investigate pivot tables as it would save a lot of typing

Cheers
JulieD


"Me" <anonymous@discussions.microsoft.com> wrote in message 
news:0bad01c50baa$9d0fc750$a501280a@phx.gbl...
> Thank you.  But I forgot to mention that my data covers
> more than 1 year.  How do I incorporate the year into my
> formula, i.e. if I need to calculate Jan 2003 and also Jan
> 2004 in another total?
>>-----Original Message-----
>>Hi
>>
>>one way
>>
>>for Jan Col B use the following formula
>>=SUMPRODUCT(--(MONTH($A$2:$A$4)=1),B$2:B$4)
>>
>>this can then be filled across for col C & D
>>for Feb
>>use
>>=SUMPRODUCT(--(MONTH($A$2:$A$4)=2),B$2:B$4)
>>
>>Etc
>>
>>alternatively, check out pivot tables - Debra Dalgleish
> has some good
>>instructions on her website at
> www.contextures.com/tiptech.html
>>
>>Cheers
>>JulieD
>>
>>
>>"Me" <anonymous@discussions.microsoft.com> wrote in
> message
>>news:0b8f01c50ba6$ce521650$a501280a@phx.gbl...
>>>I have a spreadsheet listed by day as follows.  I need
> the
>>> data from column B, C & D summarized by month. How do I
> do
>>> that?
>>>
>>> Col A      Col B   Col C  Col D
>>> 01/01/04     5       5     4
>>> 02/01/04     6       6     6
>>> 03/03/04     7       0     0
>>>
>>> I need the result to look like this -
>>> Jan 04      11      11    10
>>> Mar 04       7      0     0
>>>
>>> How do I tell it to search for dates between 01/01/04 -
>>> 31/01/04 in column A then add column B, C or D to comue
> up
>>> with my totals for Jan 04?
>>
>>
>>.
>> 


0
JulieD1 (2295)
2/5/2005 5:52:21 PM
Various ways of skinning that cat.

1) SUMPRODUCT Formulas
2) PIVOT Table
3) DATA SUBTOTALS

In this instance I would probably go with 3

Assuming your data starts in Col A with all your dates in ColA and your data
also sorted on ColA.
Insert a new ColA before A, such that it becomes your new Col A.
With your dates now starting in say B2, in A2 put
=YEAR(B2)&TEXT(MONTH(B2),"00") and copy down as far as your data goes.
Select all the data, including Col A and do Data / Subtotals, At each change
in Col A, Sum (Now tick all the boxes you can see)

This allows you to show all the data, or just the summarised data

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

----------------------------------------------------------------------------
                  It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Me" <anonymous@discussions.microsoft.com> wrote in message
news:0b8f01c50ba6$ce521650$a501280a@phx.gbl...
> I have a spreadsheet listed by day as follows.  I need the
> data from column B, C & D summarized by month. How do I do
> that?
>
> Col A      Col B   Col C  Col D
> 01/01/04     5       5     4
> 02/01/04     6       6     6
> 03/03/04     7       0     0
>
> I need the result to look like this -
> Jan 04      11      11    10
> Mar 04       7      0     0
>
> How do I tell it to search for dates between 01/01/04 -
> 31/01/04 in column A then add column B, C or D to comue up
> with my totals for Jan 04?


0
ken.wright (2489)
2/6/2005 12:15:37 PM
Reply:

Similar Artilces:

Can you search for a value output by a formula?
I want to search for a value in a cell that comes from a formula. for example, if I have =A1*A2=15.58 in cell A3. I like to do a search for 15.5 and arrive at cell A3. Is that possible? thanks. Try this: Hold down [CTRL] and press [F].......that's a shortcut for <edit><find> Find what: 15.58 Click the [Options] button Look In: Values Click [Find Next] Does that help? *********** Regards, Ron XL2002, WinXP "lbbss" wrote: > I want to search for a value in a cell that comes from a formula. for > example, if I have =A1*A2=15.58 in cell A3. I like...

sumif criteria using date
Hi, How to put in the criteria if I want to sum Column 'B' if month in column 'A' doesn't exceed MAR-2011 A B Aug-2010 300.00 Sep-2010 300.00 Oct-2010 300.00 Nov-2010 300.00 Dec-2010 300.00 Jan-2011 300.00 Feb-2011 2000.00 Mar-2011 400.00 Apr-2011 400.00 May-2011 400.00 Jun-2011 400.00 Jul-2011 400.00 If those values in column A are real dates: =sumif(a:a,"<"&date(2011,4,1),b:b) crapit wrote: > > Hi, > How to put in the criteria if I want to sum C...

remote database needs prefix database in sql
Hey All, I'm trying to write some SQL for a remote sql05 database and it requires me to prefix any table names with the database name. Is there anyway around this, kinda clutters up the sql, i think. thanks, rod. This is the way object identifiers work. :) You can, however, use synonyms on SQL Server 2005 (and above). ML --- Matija Lah, SQL Server MVP http://milambda.blogspot.com/ what does it mean when some databases require it and others don't? "ML" wrote: > This is the way object identifiers work. :) > > You can, however, use...

Workflow based on create Product entity -- possible?
Hello all, Sorry if this is an elementary question -- I'm kind of fumbling my way around on this, and I haven't found any documentation or resources on any other sites that directly answer it. The basic workflow I want to accomplish is for our accounting department to be notified (via email) when a product specialist adds a new Product to the system. I'm trying to use Workflow Manager to create a new workflow based on the create event for the Product entity type, but that type isn't in that primary list in Manager. I see maybe 25 built-in types in there, but not Product. Th...

inventory criteria
What criteria should be placed on the textbox "qty" on Form1 so that the quantity that is going to be purchased should not exceed the quantity on hand, which is shown on the textbox "unitsleft". Also if the quantity purchased exceeds the quantity on hand, what should i do so that the quantity purchased will equal the quantity on hand so that it will just purchase the remaining units instead of exceeding the quantity on hand. Is this possible? If so,pls help! Thanks! Pls reply ASAP! Really needed! Thanks again! -- ai® Message posted via AccessMonster.com http://www...

installation error, 2003 server, active dir needs to be in native mode
I was able to change exchange server from mixed to native mode but am not finding anything specific to "active directory needs to be in native mode" error message. The installation stops at this point. Any comments. Thx Jerry Chelgren Jerry, This is done from the AD admin utilies. The Implementation Guide (IG.PDF) has the directions in it. In one of the AD admin tools, you get to the properties fo the domain and then change the level of the domain. Matt Parks ---------------------------------------- ---------------------------------------- On Sat, 24 Apr 2004 04:16:51 -0...

Need help with the following code
Hi, I got the following code which is giving error in the strTSQL statement. I appreciate any help. Thanks Public Sub ExecutePassThrough1(strParam As String) Dim strTSQL As String Dim strQueryName As String strQueryName = "Query1" strTSQL = "EXEC sp_gettitles " ' & strParam & "'" -- problem with this part Call BuildPassThrough(strQueryName, strTSQL) DoCmd.OpenQuery strQueryName End Sub On Jul 20, 1:30 pm, Jack <J...@discussions.microsoft.com> wrote: > Hi, > I got the fo...

need drop-down arrow in cell
I want to create a spreadsheet for others to update by adding a row of data and filling in the required information described by column headings. Some columns will require a choice from a list of categories while others are freetext. For the category columns, is it possible to provide a combo box type list upon clicking on the appropriate cell that will display the categories and allow the user to choose one. Upon choosing the category, the value is entered in the cell. I do not want to draw a combo-box control in every cell of the sheet and I also do not want to create in input form...

Using COUNTIF with MONTH in formula
Hello, I have a number of formulas where I use the COUNTIF() formula to count the number of occurences of a particular item in a range. Now I would like to use in it conjunction with the MONTH() formula and a list of dates to count the number of dates that occur in a particular month. For example, range A1:A100 contains dates and I want to know how many of the dates occur in June. Given that MONTH returns numbers 1-12, I've been trying something like this: COUNTIF(MONTH(A1:A100),6) but I can't get it to work. I've tried using the CNTL-SHIFT-ENTER to get this to work as an...

hide rows based on cell value
I'm new to Excel and VBA. I try to hide rows based on whether the user enters a specific word(s)/phrase into a specific cell. For example, if cell M49 has a value of "i love lucy", then unhide rows 50 through 55, if cell M49 is empty or has any other value than "i love lucy", then hide the rows. How do I implement that? ' Sheet 1 Class Module Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("M49")) Is Nothing Then Exit Sub If LCase(Range("M49").Value) = "i love lucy" Then Range("A50:A55"...

If formula #3
I am trying to find an if formula that says if the result of A-B is less than ..03 then 0 but if the result is less than 0 (example -.03) than show result. The reasoning may help. We don't want to be bothered with amounts less than 3 cents but we do want to display the credit balances because we have to refund the amount. Another way of trying to explain what I am trying to do is if the result of A-B is either .01,.02,.03, than just make it 0 but if it is -.01 show result as negative. Any help would be appreciated. Try this: =IF(OR(A1-B1={0.01,0.02,0.03}),0,A1-B1) HTH, Paul -- &...

Is the IF statement what I need?
I have two cells that each have drop down lists. The first cell has the text yes and no, If i choose yes i want the second cell to return N/A for example, and if it is no I want the drop down list to be usable in the second cell. Thanks for any help Mezani< Yes, in a word. This is what I came up with: In cell A1, Data Validation: Allow: List Source: Yes, No In cell B1, Data Validation: Allow: List Source: 1,2,3,4 In cell B1, formula: "=IF(A1="Yes", &qu...

if formula #14
i have a column, i have to generate a formula usinfg a if statement that if its divisable by 3 than it should be true otherwise false =MOD(A1,3)=0 -- HTH RP (remove nothere from the email address if mailing direct) "zomex" <zomex@discussions.microsoft.com> wrote in message news:3D6B1025-CEB1-47E5-B037-02C56269FDBF@microsoft.com... > i have a column, i have to generate a formula usinfg a if statement that if > its divisable by 3 than it should be true otherwise false "zomex" wrote: > i have a column, i have to generate a formula usinfg > a if sta...

Formulas in the spreadsheet I use as a model are now incorrect.
I have a spreadsheet with formulas, columns and rows that I copy for use on a weekly basis. This week I copied the spreadsheet and the formulas are incorrect What happened? Thank you! That's nowhere near enough to go on... How did you copy it? What is incorrect about the formulae? In article <E24CAB11-20BA-4907-BB41-C57940E4B5CB@microsoft.com>, "Lollycat" <Lollycat@discussions.microsoft.com> wrote: > I have a spreadsheet with formulas, columns and rows that I copy for use on a > weekly basis. This week I copied the spreadsheet and the formulas are &g...

Need help with outlook and MSN Messenger!!!
Hi all, How do I prevent my Outlook from forwarding the emails I receive to my ISP account to my MSN Messenger's email account? Ever since I Installed office 2003, all the emails which arrive to my ISP's email account are automatically forwarded to my hotmail account and I want to put an end to it. (I am using Outlook 2003 and not outlook express...) Thanks. Bye. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ...

"You may need to log on as Admin......I already did?"
I tried to stop a startup task (quicktime). I got the following warning "an access denied error was returned while attmpeting to change a service. you may need to log on using an administrator account to make the specifidied changes" I am loged on as an administrator Not really related to Media Center (the focus of this newsgroup), this question would be better posted in a general group for whatever OS you have. However are you in a domain environment/corporate network? Network policies might be in effect. -- James Wolf [MS-MVP] Orlando, FL "I...

How do I create a formula in excel to automatically copy text fro.
Need to set up a workbook that copies text from various worksheets in the workbook into a summary page at the beginning of the workbook. Cannot figure out the forula to copy one cell of text to another cell automatically - Please help!!! hi, formulas return a value, they cannot perform an action like copy. you can have formulas on your summary page that return values from other parts of your work book. something like =sheet1!A1 this formula anywhere on your summary page will return what ever value is in cell A1 on sheet 2. >-----Original Message----- >Need to set up a workbook t...

Need Help with drop down list with conditions...
I need help. I'm having a very difficult time creating a sequence of list which change based on the item selected on the main list, lets call it the index: The Index list, the first list, would define vendors. In this case Hotel Companies (e.g Hilton, Marriott, Sheraton). The following list, the 2nd list, would define properties, but the properties would vary based on which hotel company is selected (e.g. If Hilton, then "The Plaza, the Waldorf Astoria. If Marriott, then Marriott Marquis, Marriott Convention Center etc etc...) on list #1. And Last but not least, the 3rd list would...

I need to alphabetize entire excel document.
Please let me know the best way to do this. Thanks!! Select your range data|sort debcmk3 wrote: > > Please let me know the best way to do this. > > Thanks!! -- Dave Peterson ...

Help with Formula Please
I have the following formula in cell c1 in a spreadsheet: =IF(D1>A1,IF(B1="over","+"),IF(D1<A1,IF(B1="over","-",IF(D1<A1,IF(B1="under","+"),IF(D1>A1,IF(B1="under","-")))))) 200 over - 195 200 over + 205 200 under FALSE 205 200 under + 195 What I've done above is test the formula for all four possible outcomes. As you can see the third outcome in the table (the fourth argument in the formula) is false, but I want it to read "-". I have changed the formula around every way I can thi...

Populate field based on combo box
I know this shouldn't be so hard - but I'm having a brain ... or something! On a form, I would like to select a District Number from a combo box and have the District Name appear in a text field. My form is based on a query. I know somehow I need to add criteria to my query to point to the info in the combo box but I'm not sure exactly where to put this criteria. Should the combo box field for the District Number on the form be based on the Districts table or is this the field that comes from the query? Or is it the District Name field that comes from the query? I have neve...

How do I write a formula in excel where 1 = .05 and 2 = .75 etc.
How do I write a formula in excel where 1 = .05 and 2 = .75 and 3 = 1.23? So, if the number entered in the cell is 1 then .05 is muliplied by another cell. Hi, let's assume you enter 1 in A2, and in B2 you want 0.5 multiplied by cell A12, so in B2 enter =if(A2=1,A12*0.05) to complete your example , if none of the 3 numbers are entered I assume you want blank =if(A2=1,A12*0.05,if(A2=2,your cell*0.75,if(A2=3,your cell*1.23,""))) "CarolB" wrote: > How do I write a formula in excel where 1 = .05 and 2 = .75 and 3 = 1.23? > So, if the numb...

I need a templet or file for creating a personal checkbook.
I would like a templet or file that I can covert to Lotus 123 that will track my checks. There is a few templets out there but I can not convert them to Lotus? Any help would be appericated in advance. Mike ...

Newbie needs help with first scatter plot
I have a query with two fields: a date and a list of repeating words. Something like 1/1/2007 Red 1/3/2007 Green 2/9/2007 Blue 3/1/2007 Red 3/3/2007 Blue I want to create a simple scatter plot with Date on the X-axis and Color on the Y-axis (there's only seven possible values for the words, so I'd like each word on the Y-axis and a dot in the plot above each date where it occurs). When I use the Chart wizard and select Scatter Plot, the wizard is generating charts that plot *counts*, rather than showing a single dot for each time it occurs. I thought what I wanted to do was pre...

Formula question #4
Ok, so to bring you up to speed, here's what I've got so far: My spreadsheet contains a worksheet, which contains data from a survey Each row is a person, each column a question, and each cell contains th answer that that person gave to the question - either "yes", "no", o "undecided". Here's what I want to know how to do: I made a new worksheet that I want to display short and simpl statistical information about the first worksheet. There's one formul that I would like to use, but can't seem to figure out. What I want i to do is take one q...