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

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Haseeb Avarakkan

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393

ADVERTISEMENT

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

New Member
Joined
Mar 26, 2011
Messages
5
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192

ADVERTISEMENT

...
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.
 
Last edited:

Darlene1

New Member
Joined
Mar 26, 2011
Messages
5
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
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

New Member
Joined
Mar 26, 2011
Messages
5
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."
 

Watch MrExcel Video

Forum statistics

Threads
1,109,001
Messages
5,526,203
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top