Modify SUMIF to find data range in addition to value

I am using Excel 2007.  How can I modify sumif formula to sum only cells that 
meet the sumif and date range critiera? 

=SUMIF('1563'!$F$9:$G$55202,"groceries",'1563'!$D$9:$D$55202)

-btw, it is near impossible to search the discussion group right now to find 
recent postings.  And the nofication of replies is not working as well in the 
discussion groups.
0
Utf
2/10/2010 9:42:15 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
975 Views

Similar Articles

[PageSpeed] 46

Hi,

If you want to sum all the numbers in D9:D55202 where column F has groceries 
and column E has a date greater then the date in A1, then something like 
this should work

=sumproduct(('1563'!$F$9:$G$55202="groceries")*('1563'!$E$9:$E$55202>=A1)*('1563'!$D$9:$D$55202))

A1 has the date

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Kane" <Kane@discussions.microsoft.com> wrote in message 
news:4B5A0E3C-B00C-48C9-9EDA-4668886744D0@microsoft.com...
> I am using Excel 2007.  How can I modify sumif formula to sum only cells 
> that
> meet the sumif and date range critiera?
>
> =SUMIF('1563'!$F$9:$G$55202,"groceries",'1563'!$D$9:$D$55202)
>
> -btw, it is near impossible to search the discussion group right now to 
> find
> recent postings.  And the nofication of replies is not working as well in 
> the
> discussion groups. 

0
Ashish
2/10/2010 11:56:35 PM
Then what value is it posting again? Aren't you going to have the same 
search problems? Remember, you're also saying it's no use taking the time to 
respond to your problem, because you won't find the answer anyways.

If you're serious about wanting an answer, you'll use something other than 
the Microsoft discussion group. Google and Outlook Express come to mind.

Regards,
Fred

"Kane" <Kane@discussions.microsoft.com> wrote in message 
news:4B5A0E3C-B00C-48C9-9EDA-4668886744D0@microsoft.com...
>I am using Excel 2007.  How can I modify sumif formula to sum only cells 
>that
> meet the sumif and date range critiera?
>
> =SUMIF('1563'!$F$9:$G$55202,"groceries",'1563'!$D$9:$D$55202)
>
> -btw, it is near impossible to search the discussion group right now to 
> find
> recent postings.  And the nofication of replies is not working as well in 
> the
> discussion groups. 

0
Fred
2/11/2010 12:27:20 AM
Well, I am looking to accommodate a "date range", where A1 can be greater 
than or equal to one value and lesser than or equal to another, and match the 
value in D1 to sumif the values in F.  What should that formula look like?


"Ashish Mathur" wrote:

> Hi,
> 
> If you want to sum all the numbers in D9:D55202 where column F has groceries 
> and column E has a date greater then the date in A1, then something like 
> this should work
> 
> =sumproduct(('1563'!$F$9:$G$55202="groceries")*('1563'!$E$9:$E$55202>=A1)*('1563'!$D$9:$D$55202))
> 
> A1 has the date
> 
> -- 
> Regards,
> 
> Ashish Mathur
> Microsoft Excel MVP
> www.ashishmathur.com
> 
> "Kane" <Kane@discussions.microsoft.com> wrote in message 
> news:4B5A0E3C-B00C-48C9-9EDA-4668886744D0@microsoft.com...
> > I am using Excel 2007.  How can I modify sumif formula to sum only cells 
> > that
> > meet the sumif and date range critiera?
> >
> > =SUMIF('1563'!$F$9:$G$55202,"groceries",'1563'!$D$9:$D$55202)
> >
> > -btw, it is near impossible to search the discussion group right now to 
> > find
> > recent postings.  And the nofication of replies is not working as well in 
> > the
> > discussion groups. 
> 
0
Utf
2/16/2010 8:45:01 PM
It will look like the formula that Ashish gave you. To get an exact formula, 
you need to provide the specifics:
> A1 can be greater than or equal to one value
Which value?
> lesser than or equal to another
Which value?
> match the value in D1
What needs to match?

In general your formula will look like:
=sumproduct((Range1>=StartingDate)*(Range2<=EndingDate)*(Range3=MatchValue)*(SumRange))

The ranges cannot be full columns unless you are using XL2007.
Regards,
Fred

"Kane" <Kane@discussions.microsoft.com> wrote in message 
news:4583F44E-AFB1-4A6E-98F0-50FE86805D6B@microsoft.com...
> Well, I am looking to accommodate a "date range", where A1 can be greater
> than or equal to one value and lesser than or equal to another, and match 
> the
> value in D1 to sumif the values in F.  What should that formula look like?
>
>
> "Ashish Mathur" wrote:
>
>> Hi,
>>
>> If you want to sum all the numbers in D9:D55202 where column F has 
>> groceries
>> and column E has a date greater then the date in A1, then something like
>> this should work
>>
>> =sumproduct(('1563'!$F$9:$G$55202="groceries")*('1563'!$E$9:$E$55202>=A1)*('1563'!$D$9:$D$55202))
>>
>> A1 has the date
>>
>> -- 
>> Regards,
>>
>> Ashish Mathur
>> Microsoft Excel MVP
>> www.ashishmathur.com
>>
>> "Kane" <Kane@discussions.microsoft.com> wrote in message
>> news:4B5A0E3C-B00C-48C9-9EDA-4668886744D0@microsoft.com...
>> > I am using Excel 2007.  How can I modify sumif formula to sum only 
>> > cells
>> > that
>> > meet the sumif and date range critiera?
>> >
>> > =SUMIF('1563'!$F$9:$G$55202,"groceries",'1563'!$D$9:$D$55202)
>> >
>> > -btw, it is near impossible to search the discussion group right now to
>> > find
>> > recent postings.  And the nofication of replies is not working as well 
>> > in
>> > the
>> > discussion groups.
>> 

0
Fred
2/17/2010 12:17:41 AM
Reply:

Similar Artilces:

Problem to modify the size of PlotArea
Hello, I am trying to modify a chart so that it prints in a 20cm x 17,5cm frame. I do understand that this is governed by the plotarea.insideheight and plotarea.insidewidth properties. I also do understant that these properties are read-only. So far my code is the following: With ActiveChart .SizeWithWindow = False .PageSetup.ChartSize = xlScreenSize .PlotArea.Width = 567 .PlotArea.Height = 496 Do While .PlotArea.InsideWidth < 567 .PlotArea.Width = .Pl...

Inbox Repair Tool (Almost) ALWAYS Finds Errors
I'm running Outlook 2003 (11.5608.5703) under WinXP SP1. My Outlook.pst file is a local "Personal Folders File (97-2002)." The Office 2003 installation was an upgrade from Office 2000. Shortly after installation, I ran the Inbox Repair Tool, more or less out of curiosity. It found and repaired errors. I ran it again a few days later, and again it found errors. In fact, almost every time I run it, it finds errors. Is this normal behavior? For the record, I do not abruptly power down the machine rather than allow Windows to shut down normally. Thanks in advance for your ass...

Find and replace with condition
Help - I want to update values in a column, but this is with conditions in other cells e.g. A B C 1 blue Fred 2 black Jim 3 blue Steve change blue to red in column A, where content of B = Fred Can this be done? For i = 1 To Cells(Rows.Count,"A").End(xlUp).Row If Cells(i,"A").Value = "blue" And Cells(i,"B").Value = "Fred") Then Cells(I,"A").Value = "red" End If Next i -- HTH RP (remove nothere from the email address if mailing direct) "Stuart" &...

extracting R-squared value from a chart
Hello, I have a scatter chart with a power trendline. The R-squared value is displayed. I did find formulae that I can use to calculate the c & b value (for the equation y=c*x^b) directly from the data. My question is whether there is a formula that I can use to either: a-extract the R-squared value from the chart, or b-calculate the R-sqared value directly from the data and equation values (c & b). I hope that makes sense. It's been a looooong time since I've used Statistics, so I may have used some terms incorrectly. If so, I apologize. Let me know a...

SUMIF Multiple Sheets
I am trying to use a SUMIF statement to sum the values in cell C2 over multiple sheets if the value in A2 is a 1 but am continuing to get an error message. Here is the formula I am using "=SUMIF(Sheet2:Sheet56!A2,1,Sheet2:Sheet56!C2)". Any help would be greatly appreciated. Take a look here; http://www.mcgimpsey.com/excel/threedsumif.html In article <8E6CE515-9C66-48E0-89D4-3522A492AD52@microsoft.com>, "conger12" <conger12@discussions.microsoft.com> wrote: > I am trying to use a SUMIF statement to sum the values in cell C2 over > mu...

Autofill dates in a 3D range
I have a workbook with 53 pages (onepage for each week of the year), I want to autofill the date of each weekday across all 53 sheets, You you talking about 53 worksheets, each with a tab? If so, Ctrl+Click the right and left tabs to select them and every tab between. This will Group the worksheets. Any data entered will now be entered in all. When finished, click any tab to ungroup (or right-click and choose Ungroup). -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "Gibbo" <anonymous@discussions.microsoft.com> wrote in message news:D7F9351D-8DCC-4333-812...

Erroneous 'Enter Parameter Value' dialog after converting to Acc 2
After converting from access 97 to access 2000 when I try to open some of my reports from code I get an 'Enter Parameter Value' dialog appearing. There is no field name listed in the dialog and when I press the OK button the reports appears just as in Access 97. Is there any way to suppress this dialog from appearing? Answered in m.p.a.reports ...

EXE change in data modified
Hi Guys Have a question regarding GP. What can cause a change in the "modified date" to the dynamics.exe file? In theory, something would need to change the file. My best guess would be a service pack would change the date. -- www.fmtconsultants.com "cruesta@gmail.com" wrote: > Hi Guys > > Have a question regarding GP. What can cause a change in the "modified > date" to the dynamics.exe file? > > For GP 9.0 SP 1 the Dynamics.exe modified date is 5/16/2006 which looks to be the date the Service Pack was created (as opposed to the da...

Cutting off data in a cell
I have some users who have created a form using Excel. They have merged 16 columns to create a cell for text. They have almost 1900 characters and spaces in this cell. The problem their having is the data is being cut off on the right side. When you double click within the cell all the data appears in the cell but when you click out of the cell the data cuts off again. I've tried everything I can think of so does anyone know of a way to get all the text to display in the cell? Thank for the help, Paul select the cell, right click format cells | allignment tab | check wrap text ...

getting column and row names if a cell has a value
Hello, I am trying to pull data out of a large Excel spreadsheet to get a slimmed down version.. I have a large spreadsheet (100+ columns by 500+ rows, and 12 worksheets) that I'm trying to make sense of in order to import into another program. Each Column has a four-digit code for a "name", each row has a five- digit code for its "name." The majority of cells have a value of '0'. I am trying to get excel to look at the range, and if there is a value that is not zero in a cell, give me the column name in one column, the row name in the next column then the v...

How to create chart with TWO categories and one set of data?
One cata ategory is GROUP another category is DEPARTMENTS and the data looks as under: Dept Group Salary === ===== ==== Finance Engineers 25 Finance Engineers 40 Finance Engineers 60 HR Welder 18 HR Welder 15 HR Engineers 30 HR ...

Modifier Error
Whenever we try to go to modifier it gets the following error. The Modifier is currently unavailable because another user is editing resources in the Forms Dictionary. The Server has already been rebooted but we are still coming up with the same error. Thanks, Hi make sure that you are not sharing the Forms Dictionary with another users. Thanks BS "Jess M." <JessM@discussions.microsoft.com> wrote in message news:C56C6878-9410-4CF5-95DE-DCA0AC50EA55@microsoft.com... > Whenever we try to go to modifier it gets the following error. > > The Modifier is current...

modified timeline graph
I have a table of dates and events (and "phase"), and I want to make a timeline graph: 1-d horizontal line, no (visible) y-axis. I want the distance between the events to be spaced based on the date, and the event names to be shown at each point. The dates should be shown too, either for each event or just based on auto-scaling of the x-axis (e.g. major unit = 3 months). Here's the hard part. The events happen in different "phases", and I want to show the phases graphically somehow. I was thinking this component of the graph would be a bar chart, and I could someho...

finding double payments
Imagine an excel file with two collumns, the first one contains th vendor number, the second one the payment amount. The excel fil contains all payments made for 4 months for a company. Therefore th first collumn will contain many entries for the same vendor number a we pay our vendors once a week. Now, it so happened that we somtime payed our vendors twice. Therefore, what I am looking for is a formul that will identify double payments, i.e. double entries for vendo number and payment amount. Can anyone please help me? I've spent hour to find a solution, but my limited excel knowledge cou...

Modify name displayed in GAL
Hi all! Is there any way to change the manner in wich user are displayed in GAL? I've been aked to set DislayName to "First Last", but leave "Last First" in GAL - is there any way to do this? Thanx -- R.V. ...

modify online services
My bank recently upgraded their online services to include Direct Statements. Previously I had set up to receive Web statements. Money 2004 Omline services Manager does not give me the option to "modify services" only to "change financial institutions." Any ideas on how I can modify online banking services so I can use background banking to get Direct statements? This is how it works in M03: From account list -> Click manage online services -> click Setup online services, then select the financial institution from the list (assuming the list is updated wi...

GP7.5SP6
We're in the process of upgrading from GP7.5 SP4 to SP6. We are on MSSQL Server 7.0. My supervisor has already implemented the hot fix as describe in MBS (that's a script to update some indexes on the server if someone is using GP7.5 manufacturing on MSSQL7). When I try to import modified forms and reports I've received some errors. For instances some references on the VBA to the GUI forms (i.e. text boxes, buttons) become missing. And on the reports I've been told it couldn't find the table MOP_MO_Variance_DC_Detail_Report_Temp and MOP-Sched_Interface_Report_Before....

can,t find my e-mail address
please help reply thank you "dc columbus robbins" <robbinsnest@localnet.com> wrote in message news:eJFE6ktBIHA.324@TK2MSFTNGP04.phx.gbl... > please help reply thank you This newsgroup is for questions and answers about Microsoft Access database software. You need to find a newsgroup for the software you are using. And, just for your information, you have to be a great deal more specific about what you are doing, and what is happening, if you expect anyone to be able to give you a helpful answer. "Can't find my e-mail address" doesn't tell ...

Creating Invoice with filtered data
I have a template that is uses three sheets of data to calculate costs and then the fourth sheet is the invoice. I would like the invoice to automatically enter the line item costs for which there is a charge. For example, there are four item charges that are standard for every invoice but these come after the primary charges. The list of primary charges may consist of one line item or ten. I have been using a basic formula IF(ISTEXT(Quote!$B4),Quote!$B4,"") but then I have to manually enter all of the standard charges. I would like to enter a formula that will see that there ar...

Search / Modify / Delete . . .
In �sheet1� I have a database with theses headers �Name, Phone, Address and below are the records rows 2-150. In �sheet2� I have the data entr� form (not VBA) its on the sheet, that has a macro that writes t �Sheet1� (for storage). How can implement a search for a record and return the result t �sheet2�, with the ability to modify or delete from the databas (�sheet1�). Thanking you in advance -- Fabl ----------------------------------------------------------------------- Fable's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=218 View this thread: http://www.ex...

Setting innertext value with XMLSerializer
Hi. The question is: How do I set the value of PageElement to the innertext 'Hello World' in my xml when I use XMLSerializer? My source xml is: <book> <page number="1">Hello World</page> </book> My source classes are: <xmlroot("book")> Public class Book() <xmlIgnore()> Private m_page as PageElement <xmlElement("page")> Public Property Page() as PageElement Get return m_page End Get Set(byval value as PageElement) m_page = value End Set ...

Where do i find the sort tool in Office XP?
I cannot find the sort tool on any menu. Any ideas? You can find it in the Data Menu, next to tools. Hav a nice day :) "Ian" wrote: > I cannot find the sort tool on any menu. Any ideas? Many Thanks Ian "Lamees" wrote: > You can find it in the Data Menu, next to tools. > > Hav a nice day :) > > "Ian" wrote: > > > I cannot find the sort tool on any menu. Any ideas? Hi Ian, > I cannot find the sort tool on any menu. Any ideas? Data, sort. If it doesn't show, wait a second for the menu to expand and show all entries. If...

Modified By and Modified On fields
I am trying to customize a phone form (will need to do the same to most others) by adding Modified By and Modified On fields. After publishing the form, the fields appear on the form just fine but the lookup to a user list is greyed out. Any ideas? Am I doing something wrong? I found the answer myself. Those are system-generated values and. therefore, cannot be modified by a user "mkatsev" wrote: > I am trying to customize a phone form (will need to do the same to most > others) by adding Modified By and Modified On fields. After publishing the > form, the fields ap...

does sa have automatic Modified Forms access in GP 10?
Hello: I know that, in GP 10.0, sa is tagged with "DEFAULT USER" for Alternate and Modified windows and reports and has a Power User role. But, I imported Modifier code into GP 10.0 here on my laptop and sa automatically was able to see the modification even though the DEFAULT USER "role" did not have security access to it. Does sa automatically have access to mods, no matter what the security setup is like? Thanks! childofthe1980s While I have not tested all of the form types that can be modified, I know that when I modify a Dynamics report I have to add tha...

2 DATA RANGES IN CHARTS
I am trying to create a chart of stacked data The data I have is Class of cost Planned and Actual by month/year I want a chart that will show me actuals by class of cost and then side by side actual and planned I am not sure if i can create a chart that will range two data series. If you arrange your data properly, a lot of things in Excel are vastly simplified. To chart multiple series, put the category labels (or X values) into a column, put the Y values for one series into the column to the right, and put Y values for any additional series in the next columns after that. If the da...