SUMIF question #3

Hi,

I have a workbook with column A formatted as dates, and column B containing
numbers. I would like to know how to get excel to tell me how much the sum
of the numbers would be for a given time frame. So, for instance, if I
wanted to know the sum of the numbers in column B that have year 2006 in
column A, etc. There must be a way to do this, but I'm stumped.
Also, can the criteria in the date be narrowed to give me a sum of just,
say, a given month within a given year?

Thanks in advance,

Jim


0
9/12/2006 2:40:18 PM
excel 39879 articles. 2 followers. Follow

4 Replies
417 Views

Similar Articles

[PageSpeed] 26

Try this:

With 
Dates in A1:A30
Amounts in B1:B30

Here are 2 variations of the same formula that return the sum of amounts 
from 2006:
C1: =SUMPRODUCT((YEAR(A1:A30)=2006)*B1:B30)
or
C1: =SUMPRODUCT(--(YEAR(A1:A30)=2006),B1:B30)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Jim" wrote:

> Hi,
> 
> I have a workbook with column A formatted as dates, and column B containing
> numbers. I would like to know how to get excel to tell me how much the sum
> of the numbers would be for a given time frame. So, for instance, if I
> wanted to know the sum of the numbers in column B that have year 2006 in
> column A, etc. There must be a way to do this, but I'm stumped.
> Also, can the criteria in the date be narrowed to give me a sum of just,
> say, a given month within a given year?
> 
> Thanks in advance,
> 
> Jim
> 
> 
> 
0
9/12/2006 3:13:02 PM
Ron,

Many thanks - they both work fine. now...can you explain why?? <g>

thanks again!

Jim

"Ron Coderre" <ronREMOVETHIScoderre@bigfoot.com> wrote in message
news:0B0FA2BF-2FDD-48CA-BD50-6598DEAD6CF3@microsoft.com...
> Try this:
>
> With
> Dates in A1:A30
> Amounts in B1:B30
>
> Here are 2 variations of the same formula that return the sum of amounts
> from 2006:
> C1: =SUMPRODUCT((YEAR(A1:A30)=2006)*B1:B30)
> or
> C1: =SUMPRODUCT(--(YEAR(A1:A30)=2006),B1:B30)
>
> Does that help?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "Jim" wrote:
>
> > Hi,
> >
> > I have a workbook with column A formatted as dates, and column B
containing
> > numbers. I would like to know how to get excel to tell me how much the
sum
> > of the numbers would be for a given time frame. So, for instance, if I
> > wanted to know the sum of the numbers in column B that have year 2006 in
> > column A, etc. There must be a way to do this, but I'm stumped.
> > Also, can the criteria in the date be narrowed to give me a sum of just,
> > say, a given month within a given year?
> >
> > Thanks in advance,
> >
> > Jim
> >
> >
> >


0
9/12/2006 5:26:54 PM
>>can you explain why?? 

Sure....

One of the typical uses of SUMPRODUCT is to multiply the corresponding cells 
of 2 same-size ranges and aggregate the products.
Example:
=SUMPRODUCT(A1:A30,B1:B30)
would multiply A1 X B1, then A2 X B2, etc
then it would add up all of the results

In your case, we're tweaking one of those ranges (A1:A30) to substitute the 
cell values with a boolean (TRUE/FALSE) expression.  When TRUE/FALSE values 
are impacted by mathematical operators they convert to 1 and 0, respectively. 
 The math operator we're using is the dbl-minus-sign ( -- ). The 
dbl-minus-sign is just a standard convention that we use to indicate that we 
are converting a boolean (or numeric text) value to a number.

In this formula
C1: =SUMPRODUCT(--(YEAR(A1:A30)=2006),B1:B30)
This part: --(YEAR(A1:A30)=2006)
calculates the year for each date in A1:A30 and tests if it equals 2006.
TRUE values are converted to 1's,.
FALSE values are converted to 0's.

The end result is that each 2006 date becomes a 1 and is multiplied times 
the corresponding amount in Col_B. The Non-2006 dates become 0's, so their 
corrsponding values become 0's ( 0 x value = 0 )

The SUMPRODUCT function adds up the products (values from 2006 and zeros for 
Non-2006 values)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Jim" wrote:

> Ron,
> 
> Many thanks - they both work fine. now...can you explain why?? <g>
> 
> thanks again!
> 
> Jim
> 
> "Ron Coderre" <ronREMOVETHIScoderre@bigfoot.com> wrote in message
> news:0B0FA2BF-2FDD-48CA-BD50-6598DEAD6CF3@microsoft.com...
> > Try this:
> >
> > With
> > Dates in A1:A30
> > Amounts in B1:B30
> >
> > Here are 2 variations of the same formula that return the sum of amounts
> > from 2006:
> > C1: =SUMPRODUCT((YEAR(A1:A30)=2006)*B1:B30)
> > or
> > C1: =SUMPRODUCT(--(YEAR(A1:A30)=2006),B1:B30)
> >
> > Does that help?
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP
> >
> >
> > "Jim" wrote:
> >
> > > Hi,
> > >
> > > I have a workbook with column A formatted as dates, and column B
> containing
> > > numbers. I would like to know how to get excel to tell me how much the
> sum
> > > of the numbers would be for a given time frame. So, for instance, if I
> > > wanted to know the sum of the numbers in column B that have year 2006 in
> > > column A, etc. There must be a way to do this, but I'm stumped.
> > > Also, can the criteria in the date be narrowed to give me a sum of just,
> > > say, a given month within a given year?
> > >
> > > Thanks in advance,
> > >
> > > Jim
> > >
> > >
> > >
> 
> 
> 
0
9/12/2006 5:58:01 PM
Ron, that makes perfect sense...thank you for the simple, logical and
eloquent explanation. My only remaining question would be that if the second
example works so well (as it does), is there a particular reason you added
it as an alternative to C1: =SUMPRODUCT((YEAR(A1:A30)=2006)*B1:B30) ? Does
the lack of the -- imply that it wouldn't work under certain circujmstances?

Thanks again for all your help. I am really starting to get into more
functions of excel as time goes by, and I'm finding this a very helpful
place. I also bought a "for dummies" book, Excel all in one desk reference
that I intend to read very soon. Are there any basic publications or sites
you'd suggest?

Jim

"Ron Coderre" <ronREMOVETHIScoderre@bigfoot.com> wrote in message 
news:AF9DF081-21B9-41FC-895E-0C0F73162384@microsoft.com...
>>>can you explain why??
>
> Sure....
>
> One of the typical uses of SUMPRODUCT is to multiply the corresponding 
> cells
> of 2 same-size ranges and aggregate the products.
> Example:
> =SUMPRODUCT(A1:A30,B1:B30)
> would multiply A1 X B1, then A2 X B2, etc
> then it would add up all of the results
>
> In your case, we're tweaking one of those ranges (A1:A30) to substitute 
> the
> cell values with a boolean (TRUE/FALSE) expression.  When TRUE/FALSE 
> values
> are impacted by mathematical operators they convert to 1 and 0, 
> respectively.
> The math operator we're using is the dbl-minus-sign ( -- ). The
> dbl-minus-sign is just a standard convention that we use to indicate that 
> we
> are converting a boolean (or numeric text) value to a number.
>
> In this formula
> C1: =SUMPRODUCT(--(YEAR(A1:A30)=2006),B1:B30)
> This part: --(YEAR(A1:A30)=2006)
> calculates the year for each date in A1:A30 and tests if it equals 2006.
> TRUE values are converted to 1's,.
> FALSE values are converted to 0's.
>
> The end result is that each 2006 date becomes a 1 and is multiplied times
> the corresponding amount in Col_B. The Non-2006 dates become 0's, so their
> corrsponding values become 0's ( 0 x value = 0 )
>
> The SUMPRODUCT function adds up the products (values from 2006 and zeros 
> for
> Non-2006 values)
>
> Does that help?
> ***********
> Regards,
> Ron
>
> XL2002, WinXP
>
>
> "Jim" wrote:
>
>> Ron,
>>
>> Many thanks - they both work fine. now...can you explain why?? <g>
>>
>> thanks again!
>>
>> Jim
>>
>> "Ron Coderre" <ronREMOVETHIScoderre@bigfoot.com> wrote in message
>> news:0B0FA2BF-2FDD-48CA-BD50-6598DEAD6CF3@microsoft.com...
>> > Try this:
>> >
>> > With
>> > Dates in A1:A30
>> > Amounts in B1:B30
>> >
>> > Here are 2 variations of the same formula that return the sum of 
>> > amounts
>> > from 2006:
>> > C1: =SUMPRODUCT((YEAR(A1:A30)=2006)*B1:B30)
>> > or
>> > C1: =SUMPRODUCT(--(YEAR(A1:A30)=2006),B1:B30)
>> >
>> > Does that help?
>> > ***********
>> > Regards,
>> > Ron
>> >
>> > XL2002, WinXP
>> >
>> >
>> > "Jim" wrote:
>> >
>> > > Hi,
>> > >
>> > > I have a workbook with column A formatted as dates, and column B
>> containing
>> > > numbers. I would like to know how to get excel to tell me how much 
>> > > the
>> sum
>> > > of the numbers would be for a given time frame. So, for instance, if 
>> > > I
>> > > wanted to know the sum of the numbers in column B that have year 2006 
>> > > in
>> > > column A, etc. There must be a way to do this, but I'm stumped.
>> > > Also, can the criteria in the date be narrowed to give me a sum of 
>> > > just,
>> > > say, a given month within a given year?
>> > >
>> > > Thanks in advance,
>> > >
>> > > Jim
>> > >
>> > >
>> > >
>>
>>
>> 


0
9/13/2006 4:00:04 AM
Reply:

Similar Artilces:

sumif formula #2
At the moment I am trying to work a sumif formula The problem I am encountering is once I have set the range and criteri it won't pick up the sum range as the range I am specifying is fo example J15:CB45 - it will pick up J15:CB15 but I need it to pick u the whole range - do you know of anyway I can resolve this. My formula is =sumif($J$3:$CB$3,E$3,($J15:$CB45)) but it doesn't see to work this one does though =sumif($J$3:$CB$3,E$3,($J15:$CB15)) bu that doesn't help me! I have put a print screen of the spreadsheet I am trying to work on. Thanks Jenni -- Message posted from htt...

Graph/Chart Question
I do not use the chart/grph feature too often. I was trying to use the Chart Wizard to graph the following data: A B C 3-Jan 6.16% 482202 7822780 4-Jan 5.87% 398392 6788552 5-Jan 2.60% 365581 14064487 8-Jan 5.89% 359353 6102299 9-Jan 3.80% 372896 9812270 10-Jan 4.42% 402357 9102141 I think I need to have 2 different scales (% and Natural). I could not figure out how to graph this data. Thank you in advance. On Thu, 12 Jul 2007, in microsoft.public.excel.charting, carl <carl@discussions.microsoft.com> said: >I do not use the chart/grph feature too often. I was trying to use th...

HA design question 07
We are looking to use CCR on our Mailbox servers, but we only have one CAS server in the currently design. If we loose the HAS server can the Outlook 2007 client still connect to the mailbox? Do the Outlook 2007 clients connect directly to the Mailbox server or through the CAS server? My understanding is that Outlook will still function if you lose your only CAS. OWA, Activesync, and Autoconnect will fail though. Question: If you're building a HA environement you want at least two HT servers, right? Install your CAS role on your two HT servers and voila. "Ryan Sanders&qu...

preparing for an exam and have a question
Hi! I'm prepering for an 70-536 exam and in this book is the question below. I asked this question before but didn't include all the alternatives and the book motivation so this is the complete question with the books motivation. You need to create a simple class or structure that contains only value types. You must create the class or structure so that it runs as efficiently as possible. You must be able to pass the class or structure to a procedure without concern that the procedure will modify it. Which of the following should you choose ? A. A reference class ...

Update question
Soory if this is the wrong place to ask this, but cannot find a NG proper to Windows 7. I currently run Windows Vista and am getting tired of Vista's decision to tell me that it is going to shut down in less than a minute. So have decided to upgrade to Windows &. I note that I can purchase an upgrade versiom for 64 quid from Amazon or an apparently full version of Windows 7 Home premium for 89 quid. My question is this - If I buy the upgrade version will I only be able to load it on a new PC in the future if I already have windows Vista installed? i.e For any future cl...

Date question
My macro is embedded in an Excel workbook. On 'Sheet1' there are 2 input fields, (1) the 'Start' date, and (2) the 'End' date (for a 1 week period). Users will enter both dates. There are several restrictions: (1) the 'Start' date must be a Sunday, (2) the 'End' date must be a Saturday. and (3) these dates can only be for a 1 week period (7 days). There are edits already in place to identify issues for these 3 items. It will also be okay for users to enter a week in the future. However, they cannot enter last week's dates. ...

Help!larger than A4 brochure that folds in half & across 3 times
I want to be able to fold the paper in half and then proceed with a standard three column brochure that would have information on all sides. I cannot find and template in publisher. Not sure what you are trying to do. Setup your page as a booklet, in the arrange menu setup 3 columns. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Aine Mc Laughlin" <AineMcLaughlin@discussions.microsoft.com> wrote in message news:D2FACA3F-D569-445A-AC97-85371675F3CE@microsoft.com... >I want to be able to fold the paper in half ...

SUMIF or SUMIFS
Here is what I have Col A Col B Col C 1310 3 3,463.00 1315 3 740 1330 3 1369 3 -178 1375 3 -105 1640 3 110 135 4 1310 4 1,460.00 1315 4 1,521.00 1375 4 -65 1310 6 3,284.96 I am trying to figure out a way to add column C to a new cell if Column A is between 1310 and 1369. Any suggestions?? =if(and(A1>1310;A1<1369);C1;"") HTH "Jeff" wrote: > Here is what I have > > > Col A Col B Col C > 1310 3 3,463.00 > 1315 3 740 > 1330 3 > 1369 3 -178 > 1375 3 -105 > 1640 3 110 > 135 4 > 1310 4 1,460.00 >...

Several questions about List control
Hi Friends, I got several questions, please help. 1) How to prevent the cursor from changing into a "hand" when it approaches the checkbox in the list control? 2) I have troubled making the checked checkbox. "LVIS_SELECTED" doesn't seem to work m_list.SetExtendedStyle (LVS_EX_CHECKBOXES |LVS_EX_ONECLICKACTIVATE| LVS_EX_FULLROWSELECT ); for (int i=0; i< 7; i++) { int nPos = m_list.InsertItem(i, "test"); m_list.SetItemData(nPos, LVS_ALIGNLEFT );//|LVS_NOLABELWRAP ListView_SetItemState (m_list.m_hWnd, nPos, LVIS_SELECTED, NULL); } 3) How to prevent the...

Counting question
In column A I have set of characters that are entered into the rows below (i.e. Y, N, MRO) in column B I have another set of characters that are entered into the rows below (i.e. A, E, on so on). I want to count the instances when the rows in Column A contain a Y and the rows in column B contain an A. Is there an simple formula for doing this? Any help will be appreciated. -- John =COUNTIFS(A:A,"Y",B:B,"A") Regards Steve "John" <John@discussions.microsoft.com> wrote in message news:62A26E1C-E421-41E6-834C-B8547707E2D4@microsoft.c...

Technical Question
Hi all I have a main server with some agents which each agent acts like a server. in each agent SqlServer is installed. How can I integrate all agents's data into main server. I know a solution is to merge them. Is there a better way to merge them ?? thanks in advance pers What is the vesrion of SQL Server are you using? "pers" <rezarms@hotmail.com> wrote in message news:ugfM8bhrKHA.3408@TK2MSFTNGP06.phx.gbl... > Hi all > I have a main server with some agents which each agent acts like a > server. > in each agent SqlServer is installed. ...

RMS 3.0 #2
i upgraded my service plan last year same time becuase they said rms 3.0 is releasing soon. Should i pay the fee becuase it didn't get me anything. any suggestions? Also rms 2.0 didn't do much except some button in po's and created more problems. i dont think they will be releasing 3.0 anytime soon anyway. Tough call I have heard from a partner that the 3.0 wont be out until endo 2009 but most likely 1st qtr 2010... Dynamics pos is the next release then RMS 3.0...thats on the rumor mill.. take it with a grain of salt. "manoj" wrote: > i upgraded my...

HELP: directory question
Thisw may be off topic. Sorry if it is. I've got an application, and it gets installed to a particular directory (they choose) on a users computer. How do I find out what this directory is at runtime? I want them to install a license file, and then my application check the values in the license file at runtime. Problem is, the directory could be different for each user. Hamish Dean >-----Original Message----- >Thisw may be off topic. Sorry if it is. > >I've got an application, and it gets installed to a particular directory >(they choose) on a users computer. How ...

VPN question....
HI, its possible to access my RMS databse wich is located in other city from my local office RMS Manager via VPN? is that doable? thanks! I see, since it will be the VPN network i can just type the regular server name, and database name in the RMS ADMON. Im in the right way? Thanks@ So is that perfectly possible to accomplish? I think i must use the 1433 port right? Thanks "Rob" wrote: > Yes, once you setup your VPN you need to set the SERVER Name as the LAN IP > address of the SERVER in SO ADMIN. > > Rob > > "Aldo" wrote: > > > HI,...

sumif across multiple sheets in excel 2007 based on a condition
I have a small work book, tracks deliveries made and trucks used. I have seven sheets Saturday thru Friday and one sheet that totals everything up! The weekly sheets have 34 rows representing 34 stores that we service and the total sheet also has 34 rows that contain the totals for that week. Here is my question I am trying to, on the totals sheet, sum up the total trucks that we used per store per week. In the cell would be entered 0, 0.5, 1, 2 and so on depending on how many trucks we used for that day. Yes we use 0.5 if the store was combo'ed with another store. I ne...

Merging ntext fields removes carrage returns, additional spaces and line breaks CRM 3.0 and 4.0
We have been dealing with this issue for a long time and are still exhibiting the issue in CRM 4.0. When merging some description fields (ntext) from our case records all the line feeds, carriage returns, extra spaces (more than a single space in a row) are removed and the field is merged as one paragraph of text. Can anyone explain why this occurs and how to fix it? -- Gary Herbstman Byte Solutions, Inc. http://bytesolutions.com I would try test merging two lead records, both description fields (ntext) with carriage returns (enter these values from the CRM UI only). Pick the oth...

Detect & Repair Question Please
I am running XPPro & Office 2000 Small Business edition. Every so often Outlook refuses to start & I must run "detect & repair" and that fixes it okay (I still don't know what's causing this however). My question is: when I chose "detect & repair" I an asked to put in my original installation disc. This is a pain. Surely whatever detect & repair is looking for can be installed on my hard drive so I don't have to keep going back to the install disc. Help please. Thanks in advance... Bob ...

another sumifs plea
I'm struggling to convert a sumifs line from 2007 to excel 2003. The line I have working correctly in 2007 is: =SUMIFS('Washing MC Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"= 0.00",'Washing MC Rental'!$L$4:$L$21,">28/2/2010")-SUMIFS('Washing MC Rental'!$K$4:$K$21,'Washing MC Rental'!$Q$4:$Q$21,"= 0.00",'Washing MC Rental'!$L$4:$L$21,">31/3/2010") Can any one please help me to put this into 2003? Best Regards Dave =SUMPRODUCT('Washing MC Rental'!$K$4:$K$21,...

Question on Printer Setup
I have two printers, one POS receipt printer and one laser printer. What i would like to do is is just use the laser printer because it has the 8 1/2 X 11 paper and i can put more info like company policy, items and with descriptions etc. My problem is i need a way to open the drawer and the only way would be is print out with the pos receipt printer. Is there a way to print out with both at the same time so the receipt printer will open the drawer and the laser would be for the invoice? Or does anybody have a solution for this? This is a multi-part message in MIME format. ------=_N...

SUMIF or SUMIFS help
I have 2 sheets in one workbook (Sheet 1 and Sheet 2) Sheet 2 has 3 columns: A B C MAKE TYPE QTY 1 toyota compact 10 2 ford pickup 15 3 toyota sedan 20 4 toyota pickup 80 5 nissan hybrid 10 Sheet 1 has 2 columns: A B MAKE PICKUPS 1 toyota ? (SUM) I need Sheet 1,B1 to calculate the total number of matching items in sheet 2 that matches the data entered in Sheet 1,A1. In other words, I need sheet 1,B1 to automatically sum up the total number of to...

OU Question!!!
We have installed CRM 1.2 on dev server using existing database(backup of prod db). While installation we pointed Organisation Unit to "CRMDev" (created manually under Active directory). but once installation is completed i dont see any Organisation name and usergroups under "CRMDev" Organisation Unit. Any reason why it didn't create? Is it using the same OU as prod. Is there any setting that needs to be changed in DB and point it to new OU. Please Suggest... Where we did it wrong: As posted in another post suggestion to -create a OU in AD called devcrm -backup...

Hardware Question
Hi, I have been running a Microsoft Exchange cluster for close to 5 years now. It is running on a Storagetek SAN attached (FC) to a Storagetek Library (FC). Well its time to upgrade my systems due that I cannot renew my warranty them past next year again. So I been starting to shop around for new hardware and I was wondering what you guys think about iSCSI SAN Storage solution to cluster my 3 Storage Groups that add up to 120gigs. We have 500 employees. So my plan is to get a iSCSI Tape Library and iSCSI SAN. What do you guys think and what models should I look into? George Spiro ...

sumif vs sumproduct question
Hi, I have a simple spreadsheet with column B containing all dates, and column D containing all numerical values. The dates range throughout a year. At the bottom of the sheet I've entered January through December in another column. To the immediate right of the month named, I have a formula which gives me the total for that month. For instance, next to January my formula reads: =sumproduct(--(month(b6:b370)=1),d6:d370) this formula works, but it seems to me I should be able to use SUMIF too. I've tried =sumif(d6:d370,month(b6:b370)=1,d6:d370) but that just returns a blank ce...

account blocked error code: 550 5.3.4
I get this error message when trying to send email. I don't believe that I have violated the term. Please unlock my account. 550 5.3.4 Requested action not taken; This account is currently blocked from sending messages. If you don't think you've violated the Windows Live Terms of Use, please contact customer support. -- Greeting earthlings Contact your ISP's Customer Support and not WLM -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. "1martian" <1martia...

crm 1.2 to crm 3 upgrade time out
CRM upgrade from 1.2 to 3 is failing with a timeout issue on a very large db (almost 16Gb). Any ideas? Event Logs: 15:06:31| Info| Adding attribute :StatusCode To Entity : Email 16:06:55| Error| Install exception.System.Exception: Action Microsoft.Crm.Setup.Server.InstallDatabaseAction failed. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlCli...