Divya has attended:
Excel Intermediate course
Word Intermediate course
Excel Advanced course
Multiple Sumproducts
Hi,
I have a table to Months going across and Divisions going down and number data in fills in between.
e.g.
Jan Feb Mar
A 1 2 3
B 3 4 6
C 6 7 8
This is the original source of the data, and keeping the structure table the same, these numbers will change on a monthly basis.
I want to look at the month and look at the Division and where they meet in the grid, I want the to pull that into the new structured table.
I thought of using sumproduct.
RE: Multiple Sumproducts
hi Divyia
Thank you for your question. I would like to help aand have set up a similar table like yours.
Division Jan Feb Mar Apr May Jun Jul
A 115 148 996 380 425 607 845
B 699 703 314 732 303 101 629
C 584 780 471 263 371 476 821
D 119 777 602 256 256 731 387
Please can you explain a bit more. Do you want to extract a particular month and division into a separate sheet? I can show with Index and Match function but want to clarify first. Thanks.
Regards
Doug
Best STL
RE: Multiple Sumproducts
Hi Divya
Thank you for the forum question.
I have created an example worksheet with different solutions (also using Sumproduct).
Please have a look at sheet 1 in the attached workbook to see the examples.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
sumproduct.xlsxRE: Multiple Sumproducts
Hi Jens,
Your workbook did not come across.
Please resend.
Regards,
Divya Patel
RE: Multiple Sumproducts
Here's a screenshot of the Jen''s Sumproduct file showing the formula
=SUMPRODUCT((A2:A6=B14)*(B1:G1=C13)*B2:G6)
Doug
Sumproduct.docx
