Summing an area when a condition is met

[hamishp123]

Hello ,

This is my first post on the forum and I would appreciate any advice. I currently helping my parents bakery to add up the bakers list for each day of the week.

I need to sum the row for each product (A1:A86) in each column of where the product is ordered and then that result appear on the right (R4:R86).

Essentially the right column is a sorted column of goods for the baker to handle. The left hand screen is a summary tab of all the orders from various parts of the business. The right then brings all this together to create a ordered list.

I have started with a sumproduct formula, matching the right and left list of products, but I cant seem to get it to work. for example.

=SUMPRODUCT(--($A$3:$A$291=Q4)*(B3:F3)).

Any advice would be hugely appreciated, pretty new to Excel.

[AliGW]

Welcome to the forum.

From the attachment:

For example.. Total Barm should be 7

Where and why?

[Limor_OP]

Hi,
Try this perhaps in R4 :
=SUMPRODUCT(INDEX($A$1:$F$34,MATCH(Q4,$A$1:$A$34,0),2):INDEX($A$1:$F$34,MATCH(Q4,$A$1:$A$34,0),MATCH(1,1/($A$1:$L$1<>""))))

?

[Richard Buttrey]

Since the products in column Q are not the same as the product in column a on the same row all your Sumproduct is doing is multiplying the number of occurrences of the column Q found in column A, which is always 1 by the sum of the values on the same row in B:F.

You'd be better advised to add a total column G and use a SUMIFS formula (which largely replaced the need for a Sumproduct function)
i.e. =SUMIFS(G:G,A:A,Q4)

But personally I'd use a pivot table which will be more flexible when you want to analyse other information.

See attached.

Your current layout is sort of OK, but personally I'd consider creating a normalised data base range for recording your data. That means a column for each TYPE of thing and rows to record each unique record. If I was designing this from scratch I'd have columns for

Date
Shop
Method ' i.e. Values would be Shop, Click & Collect, Market, WHolesale, Other
Quantity

That layout will give you a more efficient system.

[CARACALLA]

R4=SUM(IF($A$3:$A$100=$Q4,$B$3:$F$100))

Control +shift+enter

copy down

[hamishp123]

Thankyou ever so much, very helpful all of you. I will also look into using pivot tables to improve the efficiency of the spreadsheet.

Hamish.