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

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

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,210
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
17,019
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

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,210

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,210
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."
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,017
Messages
5,834,962
Members
430,330
Latest member
drAli77

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
Top