Trying to get an IF formula to show more than one answer

[c.drysdale89]

I am trying to use a formula to show the following sentence, as the information changes monthly:

IF it is a Class A project, [B]AND[B/] is Completed, THEN show the project name.

I believe this is what I need but I think it comes out with #VALUE! because I am trying to take more than one cell into account:

=IF(AND(AS9:AS48="Completed",H9:H48="A"),F9:F48,"")

Could anyone help with this so my formula shows all project names that are completed AND class A?

P.S. I'm using a COUNTIFS formula which comes up with the same answer BUT I don't want to count the number of projects, I want the project names to show...

[Pepe Le Mokko]

Could you please post a sample sheet showing what you have and what you need?

[FinlayH]

Try:

Please Login or Register  to view this content.

Then copy down instead of trying to work with a range in one cell.

[bebo021999]

Look like you want to establish a list based on multi criterias, and IF is not a choice.
May be INDEX and SMALL can. Try to upload an example to see what we can do.

[c.drysdale89]

Hi Finlay,

I have tried what you have suggested originally. In essence yes that is what I want, but I wasn't sure if the answers could be shown in one cell rather than having to copy down....

[FinlayH]

As the others have said, would you be able to post a sheet as example? It would be helpful to see what the sheet looks like.

[c.drysdale89]

Will do Finlay et al The work I am doing is company confidential so I will make it user friendly and change the formula slightly, accordingly.

[c.drysdale89]

I have attached a sample of the data. Hope this helps....

[bebo021999]

Type this array formula in which cell you want the list in:

Please Login or Register  to view this content.

ARRAY formula must be confirmed by CTRL-SHIFT-ENTER rather than Enter only.
Drag down as far as you need

[FinlayH]

I'm not sure it is possible to do this, though I'm sure someone will correct me if I'm wrong. It might be simpler to copy down the formula above and sort the results by the new formula to get a list of completed class A projects.

[c.drysdale89]

Looking at it, I think you're right

No worries though - I think the best way to do it is a pivot table.

Thanks anyway!

[Tony Valko]

Try this array formula**

=IFERROR(INDEX(A:A,SMALL(IF(B$3:B$16="A",IF(C$3:C$16="Completed",ROW(C$3:C$16))),ROWS(A$1:A1))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

[SDCh]

If you want the result by column, modified formula from bebo021999 (array formula, use ctrl+shift+enter):

Please Login or Register  to view this content.