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!!

 

[Aladin Akyurek]

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."

I guess you have just one question:

B4:D4 houses either numeric or text or #N/A values. If there is at least one non-#N/A value in B4:D4, E4 should display YES, otherwise NO. If this is the case, which of the following does yield the desired results?

Either...

=IF(COUNT(B4:D4)+COUNTIF(B4:D4,"?*"),"YES","NO")

Or...

=IF(COUNTIF(B4:D4,NA())=COLUMNS(B4:D4),"NO","YES")

Or... Control+shift+enter, not just enter:

=IF(SUM(IF(1-ISNA(B4:D4),1)),"YES","NO")

 

[Darlene1]

All your suggested equations worked!!!

thank you so much! I never knew you could use COUNT
that way... I just tried googling "?*" and i couldn't find the meaning of it..

You've already helped out a lot, but if you don't mind... I would like to learn and understand how all your suggested functions worked? doesn't have to be thorough, just a sentence what the function is doing? =)

 

[Aladin Akyurek]

All your suggested equations worked!!!

Great. Thanks for the feedback.

thank you so much! I never knew you could use COUNT
that way... I just tried googling "?*" and i couldn't find the meaning of it..

You've already helped out a lot, but if you don't mind... I would like to learn and understand how all your suggested functions worked? doesn't have to be thorough, just a sentence what the function is doing? =)

COUNT counts numeric values, COUNTIF with "?*" counts the text bit except the formula blanks: The wildcard ? stands for a character (an alpha or a digit) and the wildcard * stands for zero or more chars or digits. COUNTIF "?*" would pick out entries like:

a
1a
'23
FAD

Note that a non-zero value in IF is read off as TRUE, so when:

IF(COUNT(...)+COUNTIF(...),"Yes","No")

is the same as:

IF(COUNT(...)+COUNTIF(...) > 0,"Yes","No")

The last formula sums any 1 that gets created for a non-ISNA outcome:

1-ISNA(X2) when X2 = #N/A ===> 0

1-ISNA(X2) when X2 is not #N/A ===> 1

Thus a SUM result greater than 0 yields "YES", otherwise NO.