Forum Discussion
excel equation help
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
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.