So if the ARRAY formula is in D1 on each of the sheets, on your summary sheet something like

=SUM(Sheet1:Sheet20!D1)

Will give you a total count of all the sheets.

There is probably a way of using =INDIRECT() to get all the sheet names, then add them into a formula for each sheet, then do a sum.

Also, there is probably a VBA solution, but my VBA skills are just about nill.

MIKE

http://www.skeptic.com/

From:

https://support.office.com/en-us/ar…

Excel features that are not fully supported in Excel Starter

Quick Access Toolbar:
You can display the Quick Access Toolbar above or below the ribbon, but you cannot add or remove commands from it.

Ribbon Customizations:
The Excel Starter ribbon cannot be customized, but you can minimize and expand it by pressing CTRL+F1

message edited by DerbyDad03

To get it to work with your data:

First use the formula:

=VALUE(LEFT(A1,FIND(” “,A1,1)-1))

In cell B1 and drag down,
Your data should now look like:

      A              B
1) 1.0 K            1.0
2) 3.0 K            3.0
3) 10.0 K           10.0
4) 3.0 K, FWHM=0.4  3.0
5) 1.5 K            1.5

Now in cell E1 enter the first Array Formula:

=INDEX($B$1:$B$5, MATCH(SMALL(COUNTIF($B$1:$B$5, “<“&$B$1:$B$5), ROW(1:1)), COUNTIF($B$1:$B$5, “<“&$B$1:$B$5), 0))

In cell D1 enter the second Array Formula:

=INDEX($A$1:$A$5, SMALL(IF(E1=$B$1:$B$5, MATCH(ROW($B$1:$B$5), ROW($B$1:$B$5))), SUM(–(E1=$E$1:E1))))

Don’t forget it’s CTRL-SHIFT-ENTER for Array formulas.

Drag down both formulas and you data should be correct.

The Sort Wizard would have been soooooo much easier. 🙂

MIKE

http://www.skeptic.com/