how to COUNT 2 columns with different critierias

Darlene1

New Member
Joined
Mar 26, 2011
Messages
5
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!!
 
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")
 
Upvote 0

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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? =)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,587
Messages
6,179,736
Members
452,940
Latest member
Lawrenceiow

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top