Solved Return Y or N across multiple worksheets

February 12, 2015 at 09:20:25
Specs: Windows 7
So I have a worksheet for each month of the year, one for each quarter and an annual sheet.
Adding each cell with a number in and putting them in the quarterly reports has been done. However, there are some cells in the monthly report that return a yes or no value.
What I want to be able to do in the 1/4ly report is to have a cell that returns the rule if there are 2 or more yes cells in the 3 monthly reports (for example JAN FEB and MAR) then show "Y"
Using excel 2010.
Thank you very much for your help.

See More: Return Y or N across multiple worksheets

Report •

February 12, 2015 at 09:22:33
Sorry, should have been more than 2 YES to return a Y or N

Report •

February 12, 2015 at 09:39:10
✔ Best Answer
Unfortunately, COUNTIF does not work across multiple sheets.

If you DAGS countif multiple sheets you'll find some options.

One simple method is brute force:

    COUNTIF(Sheet3!A1,"Yes")>1, "Yes","No")

There are also some UDF (User Defined Functions) written in VBA that are much easier to use, as long as using VBA fits in your environment.


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

   For Each ws In ThisWorkbook.Worksheets
       myCountIf = myCountIf + WorksheetFunction.CountIf(ws.Range(rng.Address), criteria)
   Next ws
End Function

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

February 12, 2015 at 09:54:31
This is what I have so far


but the best I can get is a #VALUE! warning.

Any and all help appreciated

Report •

Related Solutions

February 12, 2015 at 10:00:50
Hi Derby Dad,

Thank you fro the infor but I am afraid that I have no idea what VBA is or what I do with the text you have shown me?

Report •

February 12, 2015 at 10:05:52
Thank you derbydad, the first formula worked perfectly.

Thank you

Report •

Ask Question