Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

multiple sumproducts

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Multiple Sumproducts

Multiple Sumproducts

ResolvedVersion 2013

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.




Edited on Thu 4 Feb 2016, 11:26

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.xlsx

RE: 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

Attached files...

Sumproduct.docx

Excel tip:

Copying Values Without Formulas in Excel 2010

If you want to copy the contents of a cell but don't want to copy the formula with it then use the following simple method:

Press Ctrl+C to copy the values in the cell. On the Home tab, click Paste and then click Paste Values.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.11 secs.