Hi, i'm very confused on which formula to use when it comes to counting certain text. for example
1st sheet
Please Login or Register to view this content.
How do I extract the data from sheet 1 so I can find the amount of doors, Ideally, I would want to return the end result as
Sheet 2
Can someone possibly help me with this function?Please Login or Register to view this content.
I only understand for 1 variable, but when I need to match 2 texts, how is this possible?
Regards,
liqt
Last edited by liqt; 01-26-2017 at 08:26 PM.
Hi,
For Total Buildings use this array formula (Ctrl+Shift+Enter when inserted)
Formula:
Please Login or Register to view this content.
and for total doors (regular formulas)
Formula:Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
See attached
try this one:
This doesn't seem to work for me when I try to add more data? I get only #VALUE! or Data becomes blank like its not registered? as the end result if I try to add more letters/doors
also I want to try get the data to the 2nd sheet by using the data from 1st sheet
Last edited by liqt; 01-26-2017 at 08:55 PM.
I see you are not familiar with PivotTable.
Ok, I give up
I'm not looking for pivot table unfortunately. I've been trying to figure this out with COUNTIF/VLOOKUP and i'm so lost.
With PivotTable you can add more data and change place of the Pivot Table also.
Simply change source from ribbon and refresh
But ok if you prefer formulas
Last edited by sandy666; 01-26-2017 at 09:08 PM.
Is this even possible? it seems from TubyBTH but when I decide to change some names/values, it just goes to bust. I'm pretty sure I'm changing the correct array/cell.
If anyone can enlighten me or perhaps help me another way? using VLOOKUP as I'm trying to get across 1 spreadsheet to another.
Note that the solution from TudyBTH just covers the range of data that you started with, i.e. from A2 to B6, so if you add more data you will need to change the row references from 6 to suit.
Note also that the first formula is an array formula, so if you change anything in that formula you must use Ctrl-Shift-Enter to commit it, rather than the usual < Enter >.
Hope this helps.
Pete
Ah you are right, this worked when I tested on TudyBTH. I will try implement this to my spreadsheet accordingly - will post my result at the end.
i didn't do the shift/ctrl/enter which I forgot but may I ask why did that work rather than simply enter?
Sorry to double post but this is giving me a headache when implementing onto my spreadsheet, trying to change the correct formula but seems I cant do it.
The formula should still be ok with using another sheet right? like pulling data from 1 sheet to sheet 2.
try with this one
Sheet RESULT, cell A2 (confirm by Control+Shift+Enter) array formula
drag down as far as you need but change range first to suit.Formula:Please Login or Register to view this content.
and
Sheet RESULT cell B2 (standard formula)
Formula:Please Login or Register to view this content.
drag down and accross as far as you need but change range first to suit.
Last edited by sandy666; 01-26-2017 at 10:40 PM.
With your spreadsheet, if I decide the change the words from Building, its fine. But when I change the colour like "Blue" into QWERTY for example, the figure disappears from SOURCE sheet of yours, why is that? I don't see any colour name in your code that is bound to it? or am I missing somethingOriginally Posted by sandy666
try with this one
Sheet RESULT, cell A2 (confirm by Control+Shift+Enter) array formula
drag down as far as you need but change range first to suit.Formula:Please Login or Register to view this content.
and
Sheet RESULT cell B2 (standard formula)
Formula:Please Login or Register to view this content.
drag down and accross as far as you need but change range first to suit.
If you change colour from Red to QWERTY on SOURCE sheet you need change the same colour Red to QWERTY on RESULT sheet
On RESULT sheet colours are Headers
If you want add any colour, e.g. Grey, add it to the colour column on SOURCE sheet but you need add the same colour on RESULT sheet as next Header and drag formula to the right and down like I said in previous post.
Last edited by sandy666; 01-26-2017 at 11:03 PM. Reason: typo
oh my god, no wonder.. I didn't know you included the headers as part of the table. either i'm stupid or that it's 3am to not think correctly >_>;
I just managed to do it change it now, lets see if I can finally implement it on my own sheet now.. zzz
I appreciate all the help from everyone so far.
If you change all the values "Blue" in sheet "source" to "qwerty", unless you ALSO change cell c1 on result from "blue" to "qwerty", then the qwerty values will be missed.
OR do you ALSO want a formula that will automatically list the unique values from source column B along row 1 of sheet "result"?? If so, use this array formula in Result, B1, copied across:
=IFERROR(INDEX(SOURCE!$B$2:$B$20, MATCH(0, IF(ISBLANK(SOURCE!$B$2:$B$20), 1, COUNTIF($A$1:A1, SOURCE!$B$2:$B$20)), 0)),"")
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly brackets yourself - it won't work...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
It might be 0406 for you, but I'm currently in Thailand!!
Thank you so f**king much. This solved my issue. I been on this idea for so damn long. I appreciate everything. Sorry for the caps lock but i struggled for like 6 hours.
Location should be changed automatically
Glad to help
If problem is resolved it's always a good practice to click on Add Reputation star to person(s) who helped you (anyone else can do it also) and mark thread as Solved. Thanks
You're welcome.
Sandy: go back to sleep!!
Gleen, later, later.
At night nobody making pain in a...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks