Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

CountIfs function to call in formula

Posted on 2014-11-06
6
Medium Priority
?
305 Views
Last Modified: 2014-11-06
I have a function for a countif.  It is:

Function myCountIf(rng As Range, criteria) As Long
     Dim ws As Worksheet

     For Each ws In ThisWorkbook.Worksheets
         If ws.Name <> "Metrics" And ws.Name <> "TFSData" Then
             myCountIf = myCountIf + WorksheetFunction.CountIf(ws.Range(rng.Address), criteria)
         End If
     Next ws
 End Function

 I have a spreadsheet that has a formula in one cell.  I call this function by:  =myCountIf(B:B,A15).  This will go to every sheet in the workbook that is not in the If clause, and get the values that match the value in A15.  I need a countifs function.  I want the formula to go through each sheet not listed in the If clause, look for the value in column B that matches the A15 cell value of the current sheet And then count the value in the H column of that row.

I tried changing the function myCountifs line in the following way but it did not work:

 myCountIfs = myCountIfs + WorksheetFunction.CountIfs(ws.Range(rng.Address), criteria, ws.Range(rng.Address), criteria)  

I called it by writing:  -myCountIfs(B:B,A15,H:H,"1")

I get the #VALUE! in the cell.  Anyone know what is wrong with it?
0
Comment
Question by:Kathtg
  • 3
  • 3
6 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40426600
Quick answer:
Your function only as two arguments - a range and criteria.  Your new function needs to have the option for more, plus it has a different name (plural).

This is just a tweak of your original function; not tested:

Function myCountIfs(rng As Range, criteria As Variant, Optional rng2 As Range, Optional Criteria2 As Variant) As Long
      Dim ws As Worksheet

      For Each ws In ThisWorkbook.Worksheets
          If ws.Name <> "Metrics" And ws.Name <> "TFSData" Then
              myCountIfs = myCountIfs + WorksheetFunction.CountIfs(ws.Range(rng.Address), criteria, (w2.Range(rng2.Address), Criteria2)
          End If
      Next ws
End Function

Open in new window


Regards,
-Glenn
0
 

Author Comment

by:Kathtg
ID: 40426705
Thanks, unfortunately that didn't work for me either.  I added a parenthesis to the end, and then played with variations to no avail.  I still get the #NAME.

I was able to get this working in the formula - where I named the spreadsheets directly:
COUNTIFS(James!B:B,A20,James!H:H,"1")+COUNTIFS(Michael!B:B,A20,Michael!H:H,"1")+COUNTIFS(Komal!B:B,A20,Komal!H:H,"1")+COUNTIFS(Trina!B:B,A20,Trina!H:H,"1")

I'm going to try a variation with this in VBA, maybe I'll have better luck.  I'll post if it works.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 40426796
The #NAME error means that the function name is different than what you typed in the cell.  They both need to be myCountifs.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:Kathtg
ID: 40426808
Thanks.  I selected it from the function dropdownlist so I know I typed it correctly.  My excel has been having weird problems - like my copy,paste stopped working, etc).  Let me try a brand new workbook.
0
 

Author Comment

by:Kathtg
ID: 40426836
Workbook change didn't help but I ended up removing the myCountIfs and changing the information in myCountIf to match.  It worked!
 
Here is the final:
Function myCountIf(rng As Range, criteria, rng2 As Range, criteria2 As Variant) As Long
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Metrics" And ws.Name <> "TFSData" And ws.Name <> "TFSBugs" Then
            myCountIf = myCountIf + WorksheetFunction.CountIfs(ws.Range(rng.Address), criteria, ws.Range(rng2.Address), criteria2)
        End If
    Next ws
End Function


Thank you thank you!!
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40426870
You're welcome x 2.  You really were on the right track; I just nudged you a bit.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question