Excel COUNTIF Formula Help

Microsoft Excel 2003 (full product)
November 30, 2010 at 07:35:01
Specs: Windows XP

I have an excel formula that searches multiple columns on one spreadsheet and when the condition is met it displays the data from another on a second tab. This part of the formula works: =IF(COUNTIF('All IFC'!F85:AS85,"No")=40,'All IFC'!A85, 0)

I'm having trouble making it also display the adjacent data in Column C. I thought using this would work: =IF(COUNTIF('All IFC'!F85:AS85,"No")=40,'All IFC'!A85:C85, 0), but i am not getting results.

Would anyone know where my error is?

thanks :)

See More: Excel COUNTIF Formula Help

November 30, 2010 at 13:23:44
I'm not sure what you are trying to do with the second formul.

The first formula appears to be pretty straightforward:

If every cell in F85:AS85 contains "No", return the value in A85, if not, return 0.

Your second function basically does the same thing, but it depends on where you put the formula.

When you tell Excel to return a value from a range of cells (e.g. A85:C85) it knows that it cannot put more than one value in a single cell, so what it returns depends on where you put the formula.

If you put the formula in a column that is within the range (e.g. A, B, or C) it will return the value from that Column. In other words, if you put that formula in B10, it would return the value from B85. C10 would get you C85.

However, if you put the formula in a column that is not within the range, (e.g. Column D or beyond), you'll get a #VALUE error.

Try it!

Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •
Related Solutions

Ask Question