Forum Discussion

11delta's avatar
11delta
Copper Contributor
May 04, 2022

excel equation help

hi there, im reasonably new to the excel world so am looking for some guidance.   i am creating a multi sheet work book with configurable cell content (text) via drop down boxes on each sheet  in a...
excel
11delta's avatar
11delta
Copper Contributor
May 04, 2022

Thanks Year to date data collationQuarter 1Quarter 2Thanks Martin_Weiss  Ive hav uploaded some screen shots, hopefully this demonstrates what i would like to do. 

the first image shows all available colours for the year, second shows what colours have been selected for that quarter and the quantity of each.

the third image shows the same but includes different colours from the previous.

 

i wish to collate total number of colours selected for the year to date regardless what cell they may be placed in. i hope this can be done, i have a sample file but unsure how to upload on this forum

 

im using excel for Mac version 16.6 bu can also do this on windows using version 1908

HansVogelaar's avatar
HansVogelaar
MVP
May 05, 2022

11delta 

Here is a way to do it:

Create a list of the sheets you want to sum.

Name this range Sheets. In the screenshot below, it is E2:E5.

In B2, enter the formula

 

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!" & "C4:C9"),A2,INDIRECT("'"&Sheets&"'!" &"D4:D9")))

 

Here, C4:C9 is the range with the colours and D4:D9 the range with the quantities (these should be the same on all sheets).

Fill down.

 

 

Sample workbook attached.