Summing a row (Dynamic in length)

Hello All, please can someone assist - using my attached example.

On Sheet4 I have my P/N (part number) which can be selected from a dropdown list (Cell C6)
I also have an end date (entered in cell C7)

From this info I want a formula to calculate what I have manually entered in cell C2.

Basically, C2 needs to look at sheet 1 (Nov) and find the part number in column B - and sum the total of the row until the specified end date (30/03/2016). So in this instance it sums Row 19 and 36 - from D:DV

Is it possible to do this easily in a formula, bearing in mind the P/N may change and also the end date. The starting row to sum will always be D

Originally Posted by bebo021999

May be:

=SUMPRODUCT((Items=Sheet4!$C$6)*(Nov!$D$1:$LJ$1<=Sheet4!$C$7)*Nov!$D$4:$LJ$84)

Hi, this just produces #N/A

I tried pasting the formula into my "master" sheet - which is bigger file than I posted on here and it just returns N/A.

Is there something in teh code that could be dependant on something? What does the "Items" part mean?

TIA

Hi, this still does not work for me. I still get N/A

I have made attachement of my master file to show the error

Hi there,

Take a look at the attached workbook and see if it does what you need.

It uses a combination of "native" Excel functions and VBA functions because using the SUMPRODUCT function in VBA can sometimes be less than straightforward!

The formula entered in the worksheet is as follows:

Please Login or Register  to view this content.

Where Cell B6 contains the name of the worksheet (month) for which the calculation is being performed, Cell C2 contains the relevant Part Number, and Cell C3 contains the latest date which should be taken into consideration.

The "PartNos", "Dates" and "SubTotals" functions are defined in VBA as follows:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your worksheet layout requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M