Request To Shorten A Formula

[Mhz]

Hello, don't want to be pest on my Duplicate Questions, but I was hoping
someone could give me a shorter formula for the one I have here.

I have 31 sheets representing 31 days (DAY1 - DAY31), of the calender
in which I check for dupes in the Phone Number Column E6-E35 in all
sheets, This is the Formula that works, but I want it shorter if
Possible.. Thanks for any Help:

=SUM(COUNTIF(INDIRECT("DAY"&{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31}&"!E6:E35"),"="&E6))

 

[Bob Phillips]

=SUM(COUNTIF(INDIRECT("DAY"&ROW(INDIRECT("1:31"))&"!E6:E35"),"="&E6))

it becomes an array formula now

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

OT: Bob, Congrats !
Your pick of Italy to lift the World Cup was unerringly spot-on ..
[ ISWINNER("Italy") returned TRUE <g> ]

---

 

[Bob Phillips]

Thank-you Max, just a pity it ended as it did.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thank-you Max, just a pity it ended as it did.

Yes, ISERROR("Zizou's last act") = TRUE, unfortunately ..

---

 

[Mhz]

Thanks Bob for the formula. I'm not getting the same results as the
formula I presented, but I'll keep working at it... Thanks for your
time..

 

[Bob Phillips]

It does work. I have posted a demo at http://cjoint.com/?hnjT5ekSjd

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Mhz]

Hello Bob, Yes, it gets no simpler than the model you provided and
suprisingly, to me, it does work!

For some reason I notice the brackets " { } " outlining your formula,
when I copy and paste it, something strange happens, the actual results
change from 8 to 2 without those brackets around the formula.. Not sure
why, but I believe that has something to do with different outcome I'm
getting.

By the way, the "8 to 2" I mentioned is the count result in your test
program. The actual result under the shortened formula without those {
} brackets results in 2 instead of 8 like the long formula. Thanks In
Advance

 

[Bob Phillips]

In my original response I did add ... it becomes an array formula now ...

I did not expand on that, (wrongly it seems) assuming you would understand
(especially as you had come up with such a sophisticated original formula
<g>).

What this means is that the formula is an array formula. An array formula
should be committed with Ctrl-Shift-Enter (CSE), not just Enter. When you
commit it in this fashion, Excel inserts the surrounding {...} (braces).
Every time that you edit the formula, the braces disappear whilst in edit
mode, but you must commit with CSE again to re-set it as an array formula.

The 2 probably comes about because that is the matching values in the first
sheet.

Apologies for not explaining that first time.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Mhz]

Oh Man, what a Genious you are! ... You should definately be Paid wel
for your expertise Bob... That was the Problem.. Thanks Very Ver
Much!!

I now have a usable formula that is not a Page Long Thanks ver
much, your help is invaluable..