nested or formulas

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » NESTED 'OR' FORMULAS

NESTED 'OR' FORMULAS

resolvedResolved · Urgent Priority · Version 2007

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

RE: NESTED 'OR' FORMULAS

Hi Amanda,

Thanks so much for resolving this for me - it was driving me nuts!


Best regards
Angela


 

Excel tip:

Moving or Copying Sheets Between Workbooks in Excel 2010

Here's how to move or copy sheets between workbooks in Excel 2010:

Open the sheet you want to move or copy then on the Ribbon click the Home tab. Click Format. Under Organize Sheets, select the option Move or Copy Sheet and then choose where you want the sheet to be moved/copied to.

View all Excel hints and tips


Server loaded in 0.09 secs.