Hello,
I believe this involves an array formula but i can't get it to work.
I need a shorter formula that can count how many times the individual cells in 'classes'!B1:AO1 appear in 'Enrolment Map'!J5:GX5
right now my formula, which works, looks like this
Formula:
Please Login or Register to view this content.
there must be a shorter way to write this formula.
thanks
Last edited by dosydos; 01-14-2016 at 05:09 PM.
a sample workbook maybe helpful, otherwise, look at my formula, it may give you some idea.
Last edited by igoodable; 01-14-2016 at 05:09 PM.
@ igoodable agreed with you
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
file attatched on original post
i think that will work igoodable..thanks
Try this formula, this is not an array:
I put it in cell B2 on the "classes" tab and dragged it across to the last column.
HTML Code:
copy paste below then hold control and shift together and then hit enter to make it array formula
where ever you want to apply
=SUM(MMULT(--((classes!B1:AO1)=TRANSPOSE('Enrolment Map'!J5:GX5)),ROW(1:40)^0))
actually igoodable i am unable to put the {} when it is referring to the classes tab. Any other ideas?Originally Posted by igoodable
I tried yours hemesh but it is coming up with a value of 220... the value should be 13.
i am re uploading the file here. In cell B4 i have my very long equation which is the one i want shortened in cell B6. My guess was an array formula needed to be used.
copy paste below then hold control and shift together and then hit enter to make it array formula
where ever you want to apply
=SUM(COUNTIF(B1:AO1,'Enrolment Map'!J5:GX5))
When you use key combination of Control shift and Enter then time curly braces comes automatically confirming formula was entered as an array formula you dont type them manually
if you want to drag formula down the lock the cell references
Last edited by hemesh; 01-14-2016 at 05:37 PM.
it looks like you have to use an array formula. after you enter the formula hold control + shift + enter
Enter this formula on your sheet on b8
Note: I hate array formulas, it's a resource hog, it's acceptable on smaller data set such as this.
Please Login or Register to view this content.
Last edited by igoodable; 01-14-2016 at 05:40 PM.
This just worked thank you very much, i am very slow when it comes to arrays for some reason, cant quite wrap my head around them.it looks like you have to use an array formula. after you enter the formula hold control + shift + enter
Enter this formula on your sheet on b8
Note: I hate array formulas, it's a resource hog, it's acceptable on smaller data set such as this.Please Login or Register to view this content.
@ dosydos what's the difference in formula between post #10 and #12 and both will work in same way
in formula in post 10 it follows your range suggested.
Both formula does the same thing
it didnt work in the first formula because i was stupid and tried putting the brackets on the inside.
You can also put the brackets inside, but you cannot reference to another cell. The former formula is a regular formula, where as the latter is an array formula.
@ dosydos ! if this solves your query you can mark thread as solved
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks