Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
find instances & report neighboring values
=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) wrote in message oups.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. |
#3
|
|||
|
|||
find instances & report neighboring values
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. |
#4
|
|||
|
|||
find instances & report neighboring values
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:E10 00) This will mean that January 2006 numbers won't appear in January 2005's numbers. 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 |
#5
|
|||
|
|||
find instances & report neighboring values
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:E10 00) This will mean that January 2006 numbers won't appear in January 2005's numbers. -- Dave Peterson Jan-06 would mean Jan xx, 2006. |
#6
|
|||
|
|||
find instances & report neighboring values
|
#7
|
|||
|
|||
find instances & report neighboring values
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. |
#8
|
|||
|
|||
find instances & report neighboring values
Never mind. I think I found my error.
Thanks for all of your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking worksheets after runnning report | Excel Discussion (Misc queries) | |||
How to find the minimum value in a database with multiple values . | Excel Discussion (Misc queries) | |||
in excel, how do I find which values doesn't have a pair? | Excel Discussion (Misc queries) | |||
How do you find duplicate values in excel- 2 columns of numbers | Excel Discussion (Misc queries) | |||
To find different values in Col B corresp. to repeated vaues in c | Excel Worksheet Functions |