complex function----Help!

  • Thread starter Thread starter narp
  • Start date Start date
N

narp

I have a worksheet(1) that is setup as follows:
Col A ColB ColC
Item# Date Qty

Worksheet(2)
ColA ColB ColC ColD
Item# Start Date End Date (result)

I am trying to write a function that will look for info in cell A5 in
worksheet 2 and find the info in column A in worksheet 1 check that the date
in column B being between a date range and then returning a sum of the totals
of the corresponding rows of column C in worksheet 1. I hope this makes
sense..I'm not sure how else to explain it.
 
you should use sumproduct. a sample:

=sumproduct(--(a2:a100=g2)*(b2:b100>h2)*(b2:b100<i2),(c2:c100))

where:

g2 contaim the item code that excel will check on column A
h2 has the start date that will be check on column B
I2 has the end date
and column c has the values that you would like to sum.

change the ranges as you need.

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"narp" escreveu:
 
Thanks so much I'll try that. One other thing my boss wants---is there a way
to set it up so that you can double click on the result cell and have another
worksheet open with the info that it gathered like a pivot table does?
 
Only in Pivot tables


--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"narp" escreveu:
 
you are welcome
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"narp" escreveu:
 
Back
Top