Counting additional entries on a linked Spreadsheet

I have two worksheets, 'monthly budget' and 'summary sheet'.

Summary sheet has been set up so that any additional records added to 'Monthly Budget' will be copied using the formula below (kindly provided by Cheeky Charlie yesterday):-

=IF(ISBLANK(INDEX('Monthly Budget'!$A:$G,ROWS($1:1),COLUMNS($A:A))),"",INDEX('Monthly Budget'!$A:$G,ROWS($1:1),COLUMNS($A:A)))

I have set up a unique entry count on 'summary sheet' which is currently showing 5. If I add a new name 'Jim' to the 'Monthly budget' sheet, this is copied on the 'summary sheet' however the unique entry count remains at 5.

Can anyone shed any light on this, I'm sure I'm missing something obvious.

(I have tried copying the formula down further but get an error message)

Thanks for any assistance.

Try changing the formula to:

=SUMPRODUCT((A2:A9<>"")/COUNTIF(A2:A9,A2:A9&""))


Note: The range covers to the cell before the place the formula is in (including blanks.. and will not cause error or count blanks.

The range in the unique name count only extends to row 6 so the new name is not included in the calculation, on that basis and based on other formulae revise to:

A10: =SUMPRODUCT((A2:A8<>"")/COUNTIF(A2:A8,A2:A8&""))

On an aside given you're returning names in A you could avoid double evaluation and just use:

A2:
=INDEX('Monthly Budget'!$A$1:$G$8,ROWS($1:2),COLUMNS($A:A))
copied down

Then use a Custom Format on A2:A8 of: [=0]"";@

B2:
=IF($A2=0,"",INDEX('Monthly Budget'!$A$1:$G$8,ROWS($1:2),COLUMNS($A:B)))
applied across matrix B2:G8

The formula CC gave you is better in that it means you need only one for the entire matrix but the above approach would ever so slightly reduce number of calcs being performed (it assumes you want to display 0 for Jim in month columns even if the source sheet is yet to be completed)

To quote Ron Coderre from an obscure site:

That formula was developed by Aladin Akyurek and Harlan Grove.

...

In the COUNTIF(A1:A10,A1:A10&"") section, this part: A1:A10&"" ensures that the COUNTIF function will always return at least 1 and never 0 (which would cause the formula to error out when the division is performed.)

Consequently, the numerator for blank cells is 0, so they are not counted.
The numerator for all non-blanks is 1.
The denominator for all non-blanks is their count.

If a value appears 3 times, three of the numerators will be 1 and their respective denominators will be 3.
The SUMPRODUCT function will add those
three fractions: (1/3)+(1/3)+(1/3)=1
That's how the three occurrences only count as a single instance of a unique value.

It basically adds a null to the end of each cell... including the blanks... and then counts them. If you don't add that, then the Countif return 0's for the blanks and dividing by 0 gives Div/0! errors.

You might also want to expand the range 'Monthly Budget'!$A$1:$G$7 in your current extraction formula to cover more ground.. after 7 entries in the Budget sheet, you will not be able to get them on the summary sheet...