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
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
Question by:Kathtg
• 3
• 3
6 Comments

LVL 27

Expert Comment

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

Regards,
-Glenn
0

Author Comment

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

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

Author Comment

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

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

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

## Featured Post

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…
###### Suggested Courses
Course of the Month10 days, 13 hours left to enroll

#### 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.