Kate has attended:
Excel Advanced - Formulas & Functions course
Array formula
Hi,
I have written this formula but it is not returning the correct data, would you be able to help?
=SUM((INDEX(REG,,$C$73)="*Charity*"))
I am looking to count the number of sales in the REG set of data in column 2 ($C$73 is a lookup formula) which contains the word charity. The word 'charity' can be followed by other words, so it is not just that text in the cell.
I hope that makes sense, any help would be much appreciated!
Thanks,
Kate
RE: Array formula
Hi Kate,
Thank you for the forum question.
Try:
=countif(REG,"*" & $C$73 & "*")
I hope this is what you are looking for. If not please let me know.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Array formula
Hi Jens,
Thank you for getting back to me. Unfortunately that didn't work as that line is part of a longer formula (apologies I should have mentioned earlier). The formula is below:
=SUM((INDEX(REG,,$C$73)="Charity")*
(INDEX(REG,,$C$74)=$E$6)*
(INDEX(REG,,$C$75)="Registration")*
(INDEX(REG,,$C$76)=$B$86)*
(INDEX(REG,,$C$77)=$C$84)*
(INDEX(REG,,$C$79)<$B9)*
(INDEX(REG,,$C$79)>=$B8))
I am basically trying to count any cells which fit all of the criteria with the first being that the column in the REG table has to contain the word 'charity'. This does not have to be an exact match, but must just contain the word.
Thanks,
Kate
RE: Array formula
Hi Kate,
Sorry I do not understand what you want. The Index functions arguments =Index(array, row number, Column number). I assumed that reg was a range name. The Index function can only return the content of one cell.
What do you have in the cells C73 to C79? Do you have column numbers in the cells C73 to C79?
I do not know if you can send me the worksheet.I need to see the data or a dummy file, where the data are organised the same way as your data.
You can count with Sumproduct but you cannot sum up a count with the sum function.
If you can send me an example file, please send it to:
info@stl-training.co.uk
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Array formula
Hi Kate,
You cannot use wildcards inside a Sum function, but if you only
have the word charity in column 2 try this:
=SUM((ISNUMBER(FIND("Charity",REG)))*
(INDEX(REG,,$C$74)=$E$6)*
(INDEX(REG,,$C$75)="Registration")*
(INDEX(REG,,$C$76)=$B$86)*
(INDEX(REG,,$C$77)=$C$84)*
(INDEX(REG,,$C$79)<$B9)*
(INDEX(REG,,$C$79)>=$B8))
Please let me know if it is not working.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Array formula
Hi Jens,
I don't only have the word charity in that cell, which is my problem as there are about 4/5 different variations... All of the variations will start with the word charity (followed by various charity names), is there something I can do with that to identify them that way?
Thanks for your patience on this.
Thanks,
Kate
RE: Array formula
Hi Kate,
Sorry I didn't explain it clear. My suggestion will find Charity in the whole REG table and all variations of Charity. The problem my suggestion can give you is that if you have Charity and all variations of Charity in more than the column number you have in C3 in the REG table you will get a wrong result. It is not looking at one cell but entire REG table.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Array formula
Hi Jens,
Sorry I understand you now, unfortunately we do have the word charity in multiple columns, is there no way to make the above column specific? Alternatively, can I create a range name which is just one column? The position of where I am looking for the column shouldn't change.
Thanks,
Kate
RE: Array formula
Hi Kate,
Yes then just replace REG with the range name in the Find function.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
