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.

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.

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.

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

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.

find instances & report neighboring values
 

Then I like my answer!

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: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.

--

Dave Peterson

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.

find instances & report neighboring values
 

Never mind. I think I found my error.

Thanks for all of your help!