find instances & report neighboring values

I am attempting to sub total amounts by month for a fairly large number
of items. I would like to have excel find all instances on one date
(Oct-05 or Jan-06 for example) and report all of the values in the cell
neighboring each (ex, if I am adding the Oct-05 values, I want it to
find the Oct-05 in d-4 and d-8 and report the value for e-4 and e-8).
Once the values are reported, I need the values added . I don't care to
see each value but I do need the total to be seen.

0
10/19/2005 3:09:10 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
391 Views

Similar Articles

[PageSpeed] 39

=SUMIF(D:D,A1,E:E)

where A1 hoilds the date to test for.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


<nash.devita@gmail.com> wrote in message
news:1129734550.461754.236160@z14g2000cwz.googlegroups.com...
> I am attempting to sub total amounts by month for a fairly large number
> of items. I would like to have excel find all instances on one date
> (Oct-05 or Jan-06 for example) and report all of the values in the cell
> neighboring each (ex, if I am adding the Oct-05 values, I want it to
> find the Oct-05 in d-4 and d-8 and report the value for e-4 and e-8).
> Once the values are reported, I need the values added . I don't care to
> see each value but I do need the total to be seen.
>


0
bob.phillips1 (6510)
10/19/2005 3:15:03 PM
Hi Bob

The OP asked for totals for the month not a single day<g>

Maybe
=SUMPRODUCT(--(MONTH(D1:D1000)=MONTH(A1)),E1:E1000)
You cannot use whole columns as ranges with Sumproduct.
Change ranges to suit, but do ensure that they are of equal length.

Regards

Roger Govier


Bob Phillips wrote:
> =SUMIF(D:D,A1,E:E)
> 
> where A1 hoilds the date to test for.
> 
0
roger1272 (620)
10/19/2005 4:01:55 PM
I'm not sure if Jan-06 means January 6, 2005 or January xx, 2006.

But maybe if it's year/month:

=SUMPRODUCT(--(TEXT(D1:D1000,"yyyymm")=TEXT(A1,"yyyymm")),E1:E1000)

This will mean that January 2006 numbers won't appear in January 2005's numbers.



nash.devita@gmail.com wrote:
> 
> I am attempting to sub total amounts by month for a fairly large number
> of items. I would like to have excel find all instances on one date
> (Oct-05 or Jan-06 for example) and report all of the values in the cell
> neighboring each (ex, if I am adding the Oct-05 values, I want it to
> find the Oct-05 in d-4 and d-8 and report the value for e-4 and e-8).
> Once the values are reported, I need the values added . I don't care to
> see each value but I do need the total to be seen.

-- 

Dave Peterson
0
petersod (12005)
10/19/2005 4:53:17 PM
Dave Peterson wrote:
> I'm not sure if Jan-06 means January 6, 2005 or January xx, 2006.
>
> But maybe if it's year/month:
>
> =SUMPRODUCT(--(TEXT(D1:D1000,"yyyymm")=TEXT(A1,"yyyymm")),E1:E1000)
>
> This will mean that January 2006 numbers won't appear in January 2005's numbers.
> 
> 
> -- 
> 
> Dave Peterson

Jan-06 would mean Jan xx, 2006.

0
10/19/2005 5:42:20 PM
Then I like my answer!

nash.devita@gmail.com wrote:
> 
> Dave Peterson wrote:
> > I'm not sure if Jan-06 means January 6, 2005 or January xx, 2006.
> >
> > But maybe if it's year/month:
> >
> > =SUMPRODUCT(--(TEXT(D1:D1000,"yyyymm")=TEXT(A1,"yyyymm")),E1:E1000)
> >
> > This will mean that January 2006 numbers won't appear in January 2005's numbers.
> >
> >
> > --
> >
> > Dave Peterson
> 
> Jan-06 would mean Jan xx, 2006.

-- 

Dave Peterson
0
petersod (12005)
10/19/2005 5:49:43 PM
All I am getting is a zero. It could very well be that I just am not
following very well, however.

Here is an example from my sheet:

items from 'd'

Oct-05
Oct-05
Oct-05
Nov-05
Jan-06

their corresponding values from 'e'

$35
$10
$10
$3
$7

In this example, Oct-05 total = $55, Nov-05 = $3, and Jan-06 = $7

I think you have what I am saying (but am including the example just
incase) but I may not be following what you are saying. When I copy /
paste the equation you gave above, I only got a '0'. Is there something
that I should be changing in the equation (other than ranges as I don't
need 1000 cells worth) that I may have missed?

Thanks so very much and I am very sorry to trouble you with such a
novice issue.

0
10/19/2005 6:08:08 PM
Never mind. I think I found my error.

Thanks for all of your help!

0
10/19/2005 6:33:05 PM
Reply:

Similar Artilces:

refresh pivot report
Every time I refresh my pivot table I lose formatting that I have to re-do every time, specifically column widths, cell borders and cell formatting such as bold and wrapped. I also lose this formatting when I use my page fields drop down box to look at different groups of data. Is there anything I can do to retain my formatting in these situations? -- Julie Julie If you go into the Pivot table options you can de-select the box "Auto Format Table". This should do the trick. Roger "Julie" wrote: > Every time I refresh my pivot table I lose formatting that I...

Pivot tables remember values that are not available anymore
Hello everyone I would like to ask you is it is possible to reset the fields in a pivot table to make disapear values that they are not anymore available on the data form the selection-criteria when a fields is drill down example lets make a simple table like :: uno dos tres a q 1 s q 2 d q 3 f q 4 g q 5 a w 6 s w 7 d w 8 f w 9 g w 0 make a pivot table and drilldown "uno" (row fields), drillacross "dos" (column fields) and "tres" as Data-Items so in the selection criteria for "uno" ...

How to find all UDF calls in all workbooks
I've decided to make a few "improvements" to some of the UDFs in my personal add-in module. Now I need to find all of the calls to those UDFs in all of my workbooks, because the syntax has changed some. Is there an easy way to find them other than opening each one? I vaguely recall dealing with this before, but I can't remember if it was code or an add in. I think this will do it for you: http://www.asap-utilities.com/download-asap-utilities.php NYC public library is closing and they're ushering me out, so I can't test it now, but give it a whirl and s...

Using Access for Microsoft Navision Reporting
I am curious if there are companies that have used Microsoft Access to produce reports from Microsoft Navision 2009 (SQL-Server version) data and are willing to share their experiences with this approach. Thanks! Brad -- Brad "Brad" wrote:subed going to jail > I am curious if there are companies that have used Microsoft Access to > produce reports from Microsoft Navision 2009 (SQL-Server version) data and > are willing to share their experiences with this approach. > > Thanks! > > Brad > > > > > -- ...

Pasting cell values doesn't remove the formula
WindowsXP Excel2003 I select all, copy, go to a new sheet, edit, paste special, values..its pastes different numbers and not the number that I previously had on the sheet. If you select the cell in the original range that looks different after you paste it, what do you see in that cell? What do you see in the formula bar for that cell? Any chance you're copying a range that has hidden rows/columns? Or even pasting to a range that has hidden rows/columns? HT wrote: > > WindowsXP > Excel2003 > > I select all, copy, go to a new sheet, edit, paste special, values..its ...

Duplicate Find not finding all dupes
Hi I am using Excel 2007 and have used previous versions to manage large address data type spreadsheets. In col B ins the street number and street name, in col C the suburb and postcode. I dedupe on col B&C as sometimes you get the same street name but different suburbs hence no dupe. The majority of my data is cut and paste from and email body. I have just introduced another 100 records from the results of a website query and they are in exactly the same format and font. When I ran the dedupe it found none, but a manual check revealed 12 dupes within the 1009 line spreadsh...

Items report shows negative -1 in Available quantity field
When I run the Items (all) report by clicking the "Items" button and sort the Available Qty. field on the report, I am finding -1 items in the Available Qty. field even though we are out of these items. When I drill into the properties of the item, and go to the Inventory tab, and look at the committed field, I find that RMS has a 1 in the field and a -1 in the Available field, yet On_hand has a zero. I would like to be able to zero out the Committed and the Available fields to zero, but would also like to know how this occured to begin with? We think that we may have had a...

Convert a textbox string value into date
I need to convert a textbox string value in the form of 31-12-09 into a date in the same format, can any one help please, thank you. >>I need to convert a textbox string value in the form of 31-12-09 into a date in the same format, Converting I understand but 'into a date in the same format' is confusing. DateSerial("20" & Right("31-12-09",2),Mid("31-12-09",InStr("31-12-09","-")+1,2),Left("31-12-09",2)) If you format it for display the results is a string. If you are going to use it in calculati...

FRX Reporter
There must be some setting that is wrong in the FRx as it will let me update rows and columns for the reports, but it cannot load the existing reports or allow me to make a new report. The product is registered correctly however FRX will not provide tech support because we're using it with Micosoft Great Plains. We installed the software only a week ago. Microsoft will provide tech support for you. You can enter in a trouble ticket via CustomerSource. You should have received a login from the partner that sold you the product. One thing that I know we've run across with FRx...

crystal report error 07-14-04
when i try to open a built in reports witch come with CRM it gives an error "Object doesn't support this property or method " the page of the report is displayed and the tools above appear normaly but in the body of the report the page is blank and this massage appear "Object doesn't support this property or method " anyone have an idea or saw this before? thanks in advance ...

Graph Question. Values from different pages. Not in same cell ranges.
Hi, I have an excel work book which keeps track of players averages in a dart league. Every week gets a different page. I would like pages for individual players, that will keep track of only 2 columns on a weekly basis, which I will use to make a graph. Because the stats are sorted by high average, the players don't always stay in the same cells. The long way would be for me to reference the necessary cells, week to week. I was wondering if there is a script that can do the following pseudocode. Using C1 in the current individual player's sheet. C1 = Where "Week1.Ce...

Query for Monthly Report
I'm using SSRS to write reports, and am very new to SQL queries. I have a report that shows totals for each day in the month to-date, for two data elements. It needs to run daily via an automatic snapshot, and be distributed via e-mail subscription. As such, the time frame cannot be a user input item. The data is always a day behind, so on the first day of the month, it needs to report the previous month, not the current month. The field I need to key on is called DateTime. My current query for the month of April 2010 is: SELECT DateTime, Sum(PatientDays) AS '...

Can I print report and then delete at the same time?
I want to print the info from a sub report and have the info deleted at the same time OK. I'll bite on this troll bait ... Why? Rob Norm wrote: > I want to print the info from a sub report and have the info deleted > at the same time Define "deleted"... -- Regards Jeff Boyce Microsoft Access MVP Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offer...

How to Pass Filter from Query to Report?
Hello, I would appreciate any help with this problem that's been driving me nuts. I have a query of inventory items with only a subset of fields from the complete inventory datasheet. I have designed a report that will take the items from the query and print tags. Sometimes, I would like to print only a few tags. I apply a filter to one of the columns in the query datasheet, but this filter has no effect on the report. What can I do so that when I apply a filter to the query, it will also be reflected in the report? I was thinking of putting a formula of some kind in the filter pro...

How to export a report?
Is there any good way to export a report? I have tried to export a fairly complex report using HTML and word, but the formatting is all messed up. How do I go about exporting and getting the formatting correct? For anything other than a very basic text report, there are only two options which will retain all formatting, including any graphical elements (even such basic things as lines and borders/boxes. They are: Snapshot format - you need to export the report via the DoCmd.SendObject command, and select acFormatSNP as the output format, or pdf format - you need to install a...

Can't publish reports / none available in CRM
Hi, I've been going round in circles on this one for weeks. CRM is installed with SBS 2003, SQl 2000 and SRS all on the same server. SPS is also installed, all service packs applied (esp SQL SP4). No reports are visible in the CRM web UI, or, in SRS via /localhost/reports. All users in /localhost/reports have EVERY permission. When I run publishreports.exe using this syntax I get the following error message. C:\Program Files\Microsoft CRM\Reports>publishreports "Intellego Systems" "intel lego-sbs\ReportingGroup {91070909-67e8-4227-81c8-1f17f8ce461c}" "...

How to anchor data label show value option on Pivot Chart
I have a pivot chart and I like to show the data value above the column, however when I resort the chart with a new query, the data labels disappear and I have to go back through format data series/data labels/show value all over again. How do I get the data labels to stay and update automatically based on the new sort Leroy This is an admitted deficiency in pivot tables and charts. Next time you have to redo a chart's formatting, record a macra, then play it back every time after that. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Sol...

Generating Multiple MS Reports
I have two tables in MS Access and would like to generate a master report separated on individual pages for eahc user. Table 1: PeopleID Fields: NameID, Last Name, First Name Table 2: Contributions Fields: NameID, Date, Amount I would like to be able to generate a report that would print a page for each NameID so they can use for tax purposes that shows their contributions to a fund. Any help on writing this query and report would be helpful. Thanks, michael_quackenbush@yahoo.com wrote: >I have two tables in MS Access and would like to generate a master >report separated ...

the date value of series is incorrectly displayed
I'm drawing a 3d graph with 2 series of data. The graph is displayed as 2 separate rows of bars behind each other. In Excel 2007 the type is Colum in the first row to the far right. 1. series is a units of work. y -axis displayed in units of Thousands 2. series is a date when the units were done. x -axis. If I hover over the the date bar in the 2nd bar row I can see all the right values including the right date. the table is: 10000 1.11.2009 10000 3.11.2009 50000 10.11.2009 (in fact todate() function All the dates are displayed as 9.02.1900 if I try to dis...

Stock report
I am trying to write a report where i need to show the available quantity of an item along with the expiry dates for a selected range of receipt dates. For example, i would display the current available quantity of an item which was received in Dec 2009. The table IV10200 does have the information except for the expiry date. Any help is appreciated. Thanks. Hi JayKay, The expiration date is in table IV00300. Kind regards, Leslie "JayKay" wrote: > I am trying to write a report where i need to show the available quantity of > an item along with t...

Find Values
Hi, I have a table named cnom which has fields cname,number and total I also have a table with 10 (for explaination) fields, p1sX (X being 1-5) which is a name selected from cname in cnom and ctl1_diff_x (x again 1-5) which specifies a time but is in text format. This table also has a date field(key). I'm trying to create a report that shows the total for each cname in cnom between two specified dates. I've been trying to 0 total in cnom then add on the amount in ctl1_diff_x to total in the record where cnom is whatever is in p1sx. This is really tricky because any name could be ...

Multiplying values in columns
I want to be able to create totals in one column from entries in another column. For example if a3=10 a7=8 a11=8. I want the corresponding rows in column b to be multiplied by 18.75. ie . b3= a3*18.75 b7=a7*18.75 b11=a11*18.75 Not every row has an entry. Paste this formula in B3. =IF(A3<>"",A3*18.75,"") Now copy the A3 Formula and paste it for the remaining cells. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Ach" wrote: > I want to be able to create totals i...

SendUsing"configuration value is invalid #5
Now I'm receiving a Compile error sub or function not defined. Private Sub CommandButton1_Click() Dim iMsg As Object Dim iConf As Object Dim cell As Range Dim Flds As Variant Application.ScreenUpdating = False ' Set iConf = CreateObject("CDO.Configuration") ' iConf.Load -1 ' CDO Source Defaults ' Set Flds = iConf.Fields ' With Flds ' Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 ' Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.mail.lycos.com" ' Item("http://sch...

Set up Excel to start new instance for each new file opened.
How do I set up Excel to open a new instance of the Excel application, every time I click on a spreadsheet file. The way it works now is that it just opens another window within the same Excel application that is running. I want it to work the same way a Word does, click on the file and a new instance of the application opens and loads the document that you just clicked on. Tools>Options>View check Windows in Taskbar "Minimal_Subset" wrote: > How do I set up Excel to open a new instance of the Excel application, every > time I click on a spreadsheet file. The w...

replace cell value
how to replace sheet1. cell a1 with shhet2 cell a1 if sheet 2 cell a2 matches sheet1 cell a2 Run this small macro: Sub ReplaceIt() If Sheets("Sheet1").Range("A2").Value = Sheets("Sheet2").Range("A2").Value Then Sheets("Sheet1").Range("A1").Value = Sheets("Sheet2").Range("A1").Value End If End Sub -- Gary''s Student - gsnu200909 "hershel" wrote: > how to replace sheet1. cell a1 with shhet2 cell a1 if sheet 2 cell a2 > matches sheet1 cell a2 > . > On ...