Forums69
Topics113,543
Posts1,340,441
Members1,785
|
Most Online731 Jan 14th, 2020
|
|
|
Excel help please.
#1631936
24/04/2019 16:01
24/04/2019 16:01
|
Joined: Jan 2006
Posts: 5,988 Sunny Darlo
Wishy
OP
Forum is my life
|
OP
Forum is my life
Joined: Jan 2006
Posts: 5,988
Sunny Darlo
|
This old chestnut again! As is logical in my head, my second stop (after my google skills have failed me) is to post an Excel question on a car forum.
Here goes. I want to count how many of 4 cells end in either "R" or "F". In other words, something along the lines of (obviously this syntax doesn't work but you should get the idea) this below
=COUNTIF(RIGHT($D3:$J3), OR("R","F") )
The data that I want to test is in cells D3, F3, H3 and J3 but the cells in-between are either blank or contain dates so I don't mind if the testing is done on the cells D3,F3,H3,J3 or the range D3:J3. Either way would work.
I've come up with the fantastically over-complex looking function below which works but I'm wondering if there is a simpler (or quicker bearing in mind I need to use this formula or a variant of it across 8 columns and 2500 rows of data)
=IF(RIGHT($D3,1)="R",1,0)+IF(RIGHT($F3,1)="R",1,0)+IF(RIGHT($H3,1)="R",1,0)+IF(RIGHT($J3,1)="R",1,0)+IF(RIGHT($D3,1)="F",1,0)+IF(RIGHT($F3,1)="F",1,0)+IF(RIGHT($H3,1)="F",1,0)+IF(RIGHT($J3,1)="F",1,0).
I want this done via a cell function as oppose to a macro, any ideas or is my unwieldy albeit working solution not as bad as it looks?
Up yours Photobucket.
|
|
|
Re: Excel help please.
[Re: Wishy]
#1631938
24/04/2019 17:20
24/04/2019 17:20
|
Joined: Mar 2019
Posts: 250 North Macedonia
386ka
Club member 1814
|
Club member 1814
Making a profit
Joined: Mar 2019
Posts: 250
North Macedonia
|
I don't rely on my excel skills in my daily life, but I wanted to try them out to see if I can help you. Just tried and tested the following formula. =COUNTIF($D3:$J3;"*r")+COUNTIF($D3:$J3;"*f") You can also use COUNTIFS. The asterisk (*) is used as the wildcard character to match any character, so that's why it's used here. A question mark (?) can be used as a wild card for individual characters, so by using more of them, you can define the fixed length of a word, and its ending. Try out the formula and see if it works. PS, sometimes, the integrated help in MS Office helps a lot.
Last edited by 386ka; 24/04/2019 17:30.
|
|
|
Re: Excel help please.
[Re: Wishy]
#1631939
24/04/2019 17:35
24/04/2019 17:35
|
Joined: Dec 2005
Posts: 8,385 Lightwater, Surrey
DaveG
Club Treasurer Member 311
|
Club Treasurer Member 311
Je suis un Coupé
Joined: Dec 2005
Posts: 8,385
Lightwater, Surrey
|
The best I can come up with so far is
=SUM(--(RIGHT(D3:J3,1)="F"))+SUM(--(RIGHT(D3:J3,1)="R"))
But don't press enter after writing out (copying) the formula, press Ctrl+Shift+Enter instead so that the formula is entered as an array formula (Excel adds its own curly brackets like this to denote an array formula)
{=SUM(--(RIGHT(D3:J3,1)="F"))+SUM(--(RIGHT(D3:J3,1)="R"))}
The -- is an Excel trick to force the resulting array of logical values (eg: {FALSE,FALSE,TRUE,FALSE,FALSE,FALSE,TRUE} to be evaluated as numbers.
1996 Portofino 20vt & 2000 Pearl White Plus 1985½ & 2016 2017 Fiat 124 Spider + XF Sportbrake
|
|
|
Re: Excel help please.
[Re: 386ka]
#1631940
24/04/2019 17:40
24/04/2019 17:40
|
Joined: Dec 2005
Posts: 8,385 Lightwater, Surrey
DaveG
Club Treasurer Member 311
|
Club Treasurer Member 311
Je suis un Coupé
Joined: Dec 2005
Posts: 8,385
Lightwater, Surrey
|
Just tried and tested the following formula.
=COUNTIF($D3:$J3;"*r")+COUNTIF($D3:$J3;"*f")
In the UK we need to replace the ; with , but it works and with less characters than mine and no need to look into array formulae
1996 Portofino 20vt & 2000 Pearl White Plus 1985½ & 2016 2017 Fiat 124 Spider + XF Sportbrake
|
|
|
Re: Excel help please.
[Re: DaveG]
#1631949
25/04/2019 11:10
25/04/2019 11:10
|
Joined: Jan 2006
Posts: 5,988 Sunny Darlo
Wishy
OP
Forum is my life
|
OP
Forum is my life
Joined: Jan 2006
Posts: 5,988
Sunny Darlo
|
The winner is =COUNTIF($D3:$J3,"*r")+COUNTIF($D3:$J3,"*f") or alternatively =SUM(COUNTIF($D3:$J3,{"*r","*f"})) I'd never previously realised that Excel handled wild cards. It's funny that you mention using the in built help, it made me realise that I never even look at it these days (except if I'm using a totally new software package to me) and tend to head straight for Google and the likes of stack exchange for answers. I've been using Excel for long enough for the only option for help to have been the in built help when I first started which was probably Windows 3.1 era. At some point I intend to get my head into when you need to use array formulae. I've used them a quite a few times but only ever in response to instructions that told me I needed to and never really understood. Anyway, thank you both.
Last edited by Wishy; 25/04/2019 11:11.
Up yours Photobucket.
|
|
|
|