# 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

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

Report •

#2
February 12, 2015 at 09:39:10
 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:```=IF(COUNTIF(Sheet1!A1,"Yes")+ COUNTIF(Sheet2!A1,"Yes")+ 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.e.g. ``` 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```

Report •

#3
February 12, 2015 at 09:54:31
 This is what I have so far =IF(COUNTIF(JAN:MAR!F97,"YES")>2,"Y","N")but the best I can get is a #VALUE! warning.Any and all help appreciated

Report •

Related Solutions

#4
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 •

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

Report •