Request To Shorten A Formula

  • Thread starter Thread starter Mhz
  • Start date Start date
M

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

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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
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..
 
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
 
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)
 
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..
 
Back
Top