x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 317

# COUNTIF - two speadsheets in argument

Hello,

I currently have a formula as below.

=IF(COUNTIF(January!\$A:\$X,A138)>0,"Yes","No")

I have created another spreadsheet called February - is there a way I can add this spreadsheet to the argument so that it will display Yes or No if A138 is on either the January or February worksheet?

GISVPN
0
gisvpn
• 5
• 4
• 3
• +1
5 Solutions

IT ConsultantCommented:
=IF(COUNTIF(January!\$A:\$X,A138) * COUNTIF(February!\$A:\$X,A138)>0,"Yes","No")

HTH,
Dan
0

IT ConsultantCommented:
The formula above will work if A138 is in both January and February sheets.

If you need it to be in January OR February, try this:

=IF(OR(COUNTIF(January!\$A:\$X,A138)>0, COUNTIF(February!\$A:\$X,A138)>0),"Yes","No")
0

Author Commented:
Thank you for the posts will try this. Can I can more arguments to this as I go ? For example another one for March?
0

IT ConsultantCommented:
Yup. OR accepts more arguments. For March this would be:

=IF(OR(COUNTIF(January!\$A:\$X,A138)>0, COUNTIF(February!\$A:\$X,A138)>0, COUNTIF(March!\$A:\$X,A138)>0, ),"Yes","No")

and so on...
0

Owner (Aidellio)Commented:
(no points)
Dan is right on the money, just wanted to point out a trailing comma that's not needed

=IF(OR(COUNTIF(January!\$A:\$X,A138)>0, COUNTIF(February!\$A:\$X,A138)>0, COUNTIF(March!\$A:\$X,A138)>0, ),"Yes","No")

should be

=IF(OR(COUNTIF(January!\$A:\$X,A138)>0, COUNTIF(February!\$A:\$X,A138)>0, COUNTIF(March!\$A:\$X,A138)>0),"Yes","No")
0

IT ConsultantCommented:
Yup, copy/pasted it and forgot to delete the comma.

Thanks Rob!
0

Owner (Aidellio)Commented:
As another approach you could write your own function as a macro.  The following will loop over all the months and look for the value in A138 if it exists will return "Yes" otherwise "No".  I've attached a demo.  Also will understand if you're wanting to keep it macro free.

``````Function CalculateCount() As String
Application.Volatile
Dim curSheet As Worksheet
Dim result As String

result = "No"

For Each mth In Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
If (SheetExists(mth)) Then
Set curSheet = Sheets(mth)
If (curSheet.Range("A138").Value > 0) Then
result = "Yes"
End If
End If
Next mth

Set curSheet = Nothing

CalculateCount = result
End Function

Function SheetExists(ByVal sheetName As String)
SheetExists = False
For Each sht In ActiveWorkbook.Worksheets
If (sht.Name = sheetName) Then
SheetExists = True
End If
Next sht
End Function
``````
Book2.xlsm
0

Commented:
If you might want to search more than two worksheets, then the OR with COUNTIF approach starts growing out of hand. You can avoid that by using this formula with a named range Months that contains the names of the worksheets of interest:
=IF(SUMPRODUCT(COUNTIF(INDIRECT("'" & Months & "'!\$A:\$X"),A138))>0,"Yes","No")
0

Author Commented:
Hello,

@Dan Craciun- thank you for the formular syntax. I am using this currently:

=IF(OR(COUNTIF(January!\$A:\$X,A138)>0, COUNTIF(February!\$A:\$X,A138)>0),"Yes","No")

however it incorrectly evaluates to 'Yes' when the value in A138 is not actually on either worksheet? Is there something wrong in my formular - it works perfectly with just January looking for the value.
0

Owner (Aidellio)Commented:
Dan's formula should be:

=IF(OR(COUNTIF(January!\$A138,">0")>0, COUNTIF(February!\$A138,">0")>0, COUNTIF(March!\$A138,">0")>0),"Yes","No")

COUNTIF takes two arguments, the Range and the criteria.  In other words, count the items in A138 that are >0.  If that count is more than 0 then the whole condition returns TRUE.  If any of the COUNTIF statements are TRUE then the formula will show a YES
0

IT ConsultantCommented:
Rob, A138 is a cell, not a range. And the formula says: count the number of times the criteria (A138) is in the range \$A:\$X.

@gisvpn: can you post a sample file so we see where is the problem? Thank you.
0

Owner (Aidellio)Commented:
Sorry Dan - read it completely wrong, I interpreted incorrectly as each sheet's A138 had a value > 0

for the record though, a cell is still classified as a range
0

Author Commented:
Sorry this is actually absolutely fine! It was my mistake the formula was actually correct! :)

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.