Improve company productivity with a Business Account.Sign Up

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

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?

Thank you in advance,

GISVPN
0
gisvpn
Asked:
gisvpn
  • 5
  • 4
  • 3
  • +1
5 Solutions
 
Dan CraciunIT ConsultantCommented:
=IF(COUNTIF(January!$A:$X,A138) * COUNTIF(February!$A:$X,A138)>0,"Yes","No")

HTH,
Dan
0
 
Dan CraciunIT 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
 
gisvpnAuthor 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Dan CraciunIT 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
 
RobOwner (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
 
Dan CraciunIT ConsultantCommented:
Yup, copy/pasted it and forgot to delete the comma.

Thanks Rob!
0
 
RobOwner (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

Open in new window

Book2.xlsm
0
 
byundtCommented:
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
 
gisvpnAuthor 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
 
RobOwner (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
 
Dan CraciunIT 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
 
RobOwner (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
 
gisvpnAuthor Commented:
Sorry this is actually absolutely fine! It was my mistake the formula was actually correct! :)

Thank you for your help.
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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now