Gary Croxford
asked on
MS Excel Count B Where A = X
Thank you for looking at my question,
I have two columns in a larger worksheet Date and Confirmed. Date contains date values in the format dd-mm-yyyy and Confirmed contains the word "Yes" where relevant to the data
eg
31/07/2013
31/07/2013 Yes
31/07/2013 Yes
31/07/2013
01/12/2013
01/12/2013
I am trying to count the number of Yesses in each year and have tried
=COUNTIFS(ConversionDate,Y EAR(Conver sionDate)= 2017,Conve rsion,"Yes ")
Where ConversionDate is a dynamic range covering the date column and Confirmed is a dynamic range covering the Yes column but it keeps returning #Value.
The data in the date column are dates only and the cells in the Confirmed column contain either "Yes" or are empty
Can anybody suggest an alternative method or blow holes in the method I've chosen?
Thank you,
I have two columns in a larger worksheet Date and Confirmed. Date contains date values in the format dd-mm-yyyy and Confirmed contains the word "Yes" where relevant to the data
eg
31/07/2013
31/07/2013 Yes
31/07/2013 Yes
31/07/2013
01/12/2013
01/12/2013
I am trying to count the number of Yesses in each year and have tried
=COUNTIFS(ConversionDate,Y
Where ConversionDate is a dynamic range covering the date column and Confirmed is a dynamic range covering the Yes column but it keeps returning #Value.
The data in the date column are dates only and the cells in the Confirmed column contain either "Yes" or are empty
Can anybody suggest an alternative method or blow holes in the method I've chosen?
Thank you,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
Assuming you have Dates in Col A, I would insert another column next to it to convert Year to Date with below formula and drag down until your range:
Count-As-Per-Year.xlsx
Assuming you have Dates in Col A, I would insert another column next to it to convert Year to Date with below formula and drag down until your range:
=IF(A2="","",TEXT(A2,"yyyy"))
Then I will create a Dynamic Name Range for Col B for Years with below formula:=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$1000))
I will insert another column C for Unique Years with below Array Formula confirmed with Ctrl+Shift+Enter:=IFERROR(INDEX(Years,MATCH(0,COUNTIF($C$1:C1,Years),0)),"")
Then I will create another Named Range with below formula for Unique Years:=OFFSET(Sheet1!$C$2, 0, 0, COUNT(IF(Sheet1!$C$2:$C$1000="", "", 1)), 1)
Then In F1 I will have Data Validation List with below formula:=UniqueYears
Then finally in Column E I will have Count with below formula:=IF(A2="","",COUNTIFS($D:$D,"Yes",$B:$B,$F$1))
Please find attached for you reference:Count-As-Per-Year.xlsx
Oops I didn't see Neeraj's Code :)
Needed to enter it with ctrl,shift enter but otherwise spot on, thank youYou're welcome.
No you don't need to confirm this formula with Ctrl+Shift+Enter as Sumproduct can handle array calculation.
ASKER