Angela has attended:
Excel Advanced course
NESTED 'OR' FORMULAS
Please can you remind me the sequence for a nested formula using 'OR' and 'IF' following 'COUNTIF'
I am trying to ask Excel to allocate one point each time the word 'Gold', 'Silver' and 'Bronze' features in a particular section of my spreadsheet.
I was counting them individually:
=(COUNTIF(D2:N2,"Gold"))*1
=(COUNTIF(D2:N2,"Silver"))*1
=(COUNTIF(D2:N2,"Bronze"))*1
But there must be a way to use a combined formula which will comprise all of the above using'OR' or perhaps 'IF'..(?)
Thanks
Angela
RE: NESTED 'OR' FORMULAS
Hello Angela
Thank you for your question and welcome to the forum.
I think you could resolve this by nesting the three countif functions inside a SUM function:
=SUM(COUNTIF(D2:N2,"gold"),COUNTIF(D2:N2,"silver"),COUNTIF(D2:N2,"bronze"))
Or if the only pieces of text in the cell range D2:N2 are gold, silver or bronze, you could get Excel to count the number of entries in that cell range using =COUNTA(D2:N2).
Kind regards
Amanda
