how to COUNT 2 columns with different critierias

[Darlene1]

Hi, I use Excel 2007 & I actually have 2 questions:

1st PROBLEM:
How to COUNT/SUM of where cells that DON'T contain "#N/A" in column G & cells that DO contain "#N/A" in column F.

This is what I have and I'm trying to have the count on G11

F G
5) #N/A Archived
6) #N/A Archived
7) #N/A Archived
8) #N/A Archived
9) Green #N/A
10) #N/A #N/A
11) =F(x)

Q: IS THERE A WAY TO COMBINE THESE EQUATIONS INTO ONE?
Right now I have one column for =COUNTIF(G5:G10,"<>##N/A")
&
Another column for =COUNTIF(F5:F10,"=#N/A")


This is what I have tried
=SUM(IF(G5:G10<>"#N/A"))+SUM(IF(F5:F10="#N/A"))
=SUM(IF(G5:G10<>"#N/A",IF(F5:F10="#N/A")))
*I even tried the ctrl+shift+enter


2nd PROBLEM:
Q: after googling it seems like you can't look up have more than one cell w/ "vlookup" function, is that true?

Objective: In sheet 1, there are columns A-D with data
In Sheet 2 there are columns A-K with data, Column L is the active column w/ the f(x)
In Sheet2 L2, I want it to search Sheet2 E2. within the array of Sheet1! B:C, and then output Sheet1! C. But if they don't find anything, then Sheet2 L2 will search in Sheet2 K2, within the array of Sheet1! B:C, and then output Sheet1!C


Index/Match won't work because i'm not trying to search something w/ multiple conditions.
i'm trying to combine a "Vlookup" function with an "OR" function"

In other words, my brain is thinking:
SHEET2! L2=VLOOKUP(OR(IF(SHEET2!E2,'SHEET1'!B:C,3,FALSE),IF(SHEET2!K2,'SHEET1'!B:C,3,FALSE)))

Anyone who can help me out w/ any of these scenarios, MUCH APPRECIATION!!

 

[Haseeb Avarakkan]

Hello, Welcome to MrExcel.

Try this for your first question

=COUNTIF(F5:G10,"?*")

Or,

=COUNTA(F5:G10)

If you have any valid numbers in the range, it won't count the numbers.

2nd Question.

You can try something like,

=IFERROR(FirstVLOOKUPFormula,SecondVLOOKUPFormula)

If the 1st one couldn't find anything then 2nd one will return,otherwise 1st one.

If the both return #N/A it will return #N/A

 

[AlphaFrog]

1st Problem:
=COUNTIFS(F5:F10,"=#N/A", G5:G10,"<>#N/A")

 

[Aladin Akyurek]

1st Problem:

Either...

=COUNTIF(F5:G10,"<>#N/A")

Or...

=COUNTIF(F5:G10,"<>"&NA())

Or...

=COUNTIF(F5:G10,"?*")

2nd Problem:

What result is expected - text or number?

 

[Marcelo Branco]

2nd problem

Maybe this

=IFERROR(VLOOKUP(E2,Sheet1!$B$1:$C$100,2,0),VLOOKUP(K2,Sheet1!$B$1:$C$100,2,0))


M.

 

[Darlene1]

MY GOODNESS THIS COMMUNITY IS AMAZING!!!I always highly respected ppl. who answered these Excel forums, but I never expected so many replies and so quickly! I hope you guys get some type of perk for helping out!

For problem #2, I was using this function, which worked but only enabled one cell to be searched in two columns.
IF(ISERROR(VLOOKUP(J4,'SHEET1'!$G$1:$N$705,8,FALSE))=FALSE,VLOOKUP('SHEET2'!J4,SHEET1!$G$1:$N$705,8,FALSE),VLOOKUP('SHEET2'!J4,SHEET1!$K$1:$N$705,8,FALSE))

Thank you so much for the quick response! I just learned two new functions =)

*quick question, I've read that the ADD function can have multiple conditions.
So I have 4 columns A4-D4 w/ data. If they all equal out to #N/A, then I want E4 to be NO. But if any of the columns from A-D has a value, then I want E4 to output "Yes."

I tried
=IF(AND(A4=B4=C4=D4="#N/A"),"NO","YES")
=IF(A4="#N/A",B4="#N/A",C4="#N/A",D4="#N/A","NO","YES")
=IF(NOT(A4=B4=C4=D4="#N/A"),"NO","YES")

But none of these equations are working. Instead if all COLUMNS A-D have a value, then it'll output "yes". Any line that has a mixture of #N/A & a value, it'll output "No" instead of "yes"

Cheers!

 

[Aladin Akyurek]

...
For problem #2, I was using this function, which worked but only enabled one cell to be searched in two columns.
IF(ISERROR(VLOOKUP(J4,'SHEET1'!$G$1:$N$705,8,FALSE))=FALSE,VLOOKUP('SHEET2'!J4,SHEET1!$G$1:$N$705,8,FALSE),VLOOKUP('SHEET2'!J4,SHEET1!$K$1:$N$705,8,FALSE))

What kind of result is expected from the VLOOKUP bits?

...
So I have 4 columns A4-D4 w/ data. If they all equal out to #N/A, then I want E4 to be NO. But if any of the columns from A-D has a value, then I want E4 to output "Yes."

I tried
=IF(AND(A4=B4=C4=D4="#N/A"),"NO","YES")
=IF(A4="#N/A",B4="#N/A",C4="#N/A",D4="#N/A","NO","YES")
=IF(NOT(A4=B4=C4=D4="#N/A"),"NO","YES")

But none of these equations are working. Instead if all COLUMNS A-D have a value, then it'll output "yes". Any line that has a mixture of #N/A & a value, it'll output "No" instead of "yes"...

If A4:D4 is supposed to house text...

=IF(COUNTIF(A4:D4,"?*"),"Yes","No")

Note that this yields "no" in case the range has nothing but a formula blank (i.e., "").

If A4:D4 is supposed to house numbers...

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,A4:D4)),"Yes","No")

This also disregards the formula blanks.

 

[Darlene1]

Hi Aladin,

The equation you suggested didn't work. However, columns B-D have nested IF statements, so it can a number value, text value, or a #N/A.
Column A is just a column for Object names.

So I want column E to help me identify, when I filter, which objects have #N/A for all their fields from B-D

 

[Aladin Akyurek]

Hi Aladin,

The equation you suggested didn't work.

Which formula exactly did not work? What did it return for the sample you posted and what do you expect it to return?

However, columns B-D have nested IF statements, so it can a number value, text value, or a #N/A.
Column A is just a column for Object names.

So I want column E to help me identify, when I filter, which objects have #N/A for all their fields from B-D

Do you mean AutoFilter or what? Do you want to count such objects or just flag?

 

[Darlene1]

Both equations didn't work, Column E mostly had Yes and #REF.
However, I don't think those individual suggested equations will be doing what I want because Columns B-D can be numbers or text.

What I expect E4 to output would be if B4-D4 are all equal to #N/A, then I want E4 to say "NO."