Sumproduct and Lookup

[gary_parker]

Hi

Looking to create a formula that shows a weekly summary of each of the products within a table.

I'm currently using a SUMPRODUCT formula as follows

=SUMPRODUCT(('Scheduled Cancellations'!$A$3:$A$699>=$B5)*('Scheduled Cancellations'!$A$3:$A$699<=$C5)*('Scheduled Cancellations'!$B$3:$D$699))

$B5 and $C5 both relate to a date formula that takes first date of the week (monday) and date+7

What i'd like to do, is encorporate a lookup into the above sum product that takes away the association of set cells as currently I have to sort by column to arrange the cells in alphabetical format (product 1 is not naturally called product 1)

 

[T. Valko]

Hi

Looking to create a formula that shows a weekly summary of each of the products within a table.

I'm currently using a SUMPRODUCT formula as follows

=SUMPRODUCT(('Scheduled Cancellations'!$A$3:$A$699>=$B5)*('Scheduled Cancellations'!$A$3:$A$699<=$C5)*('Scheduled Cancellations'!$B$3:$D$699))

$B5 and $C5 both relate to a date formula that takes first date of the week (monday) and date+7

What i'd like to do, is encorporate a lookup into the above sum product that takes away the association of set cells as currently I have to sort by column to arrange the cells in alphabetical format (product 1 is not naturally called product 1)

encorporate a lookup into the above sum product

A lookup for what?

And, lookup where?

We're going to need more info!

 

[gary_parker]

Apologies

I had attempted to load an image into the posting, which was removed for some reason.

Table should be here

The sumproduct is located within a summary table that matches between two set dates ($b5 and $c5 in formula)

Therefore, the summary table for Product 1 between 11th and 17th Jan 2010 would have been 372 cancellations

What I want the formula to do is change the need for columns B-D to be product 1 and have the sumproduct formula lookup the name product 1 and do the sums

Hope that is clearer with example.

 

[barry houdini]

Assuming you have data up to column Z (change as required) try

=SUMPRODUCT(('Scheduled Cancellations'!$A$3:$A$699>=$B5)*('Scheduled Cancellations'!$A$3:$A$699<=$C5)*('Scheduled Cancellations'!$B$2:$Z$2="Product 1"),'Scheduled Cancellations'!$B$3:$Z$699)

You can replace "Product 1" in the formula with a relevant cell reference.....

 

[T. Valko]

Apologies

I had attempted to load an image into the posting, which was removed for some reason.

Table should be here

The sumproduct is located within a summary table that matches between two set dates ($b5 and $c5 in formula)

Therefore, the summary table for Product 1 between 11th and 17th Jan 2010 would have been 372 cancellations

What I want the formula to do is change the need for columns B-D to be product 1 and have the sumproduct formula lookup the name product 1 and do the sums

Hope that is clearer with example.

Ok, now I think I understand...

Try this...

=SUMPRODUCT(('Scheduled Cancellations'!$A$3:$A$699>=$B5)*('Scheduled Cancellations'!$A$3:$A$699<=$C5)*('Scheduled Cancellations'!B2:D2="Product 1")*'Scheduled Cancellations'!$B$3:$D$699)

 

[gary_parker]

T. Valko, i've found that Barry Houdinis example works, I think due to the lack of the * on the final criteria. Both, thanks for the insight on this, its been most helpful.

On a separate note, but related to the same problem. How can (if at all) the formula be developed to exclude those that are classed as "Additional" cancellations and focus only on the addition of "Expected+Actual"

Thanks

 

[T. Valko]

T. Valko, i've found that Barry Houdinis example works, I think due to the lack of the * on the final criteria.

If there is any non-numeric data in the sum range then the syntax I suggested will return a #VALUE! error.

On a separate note, but related to the same problem. How can (if at all) the formula be developed to exclude those that are classed as "Additional" cancellations and focus only on the addition of "Expected+Actual"

Thanks

With your data in the range A1:G20...

Use cells to hold the criteria:

• I2 = lower date boundary = 1/11/2010
• J2 = upper date boundary = 1/17/2010
• K2 = Product 1
• L2 = Expected
• M2 = Actual

Then:

=SUMPRODUCT((A3:A20>=I2)*(A3:A20<=J2)*(B2:G2=K2)*(ISNUMBER(MATCH(B1:G1,L2:M2,0))),B3:G20)