Link to home
Start Free TrialLog in
Avatar of Gary Croxford
Gary CroxfordFlag for United Kingdom of Great Britain and Northern Ireland

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,YEAR(ConversionDate)=2017,Conversion,"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,
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Gary Croxford

ASKER

Needed to enter it with ctrl,shift enter but otherwise spot on, thank you
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:
=IF(A2="","",TEXT(A2,"yyyy"))

Open in new window

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

Open in new window

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)),"")

Open in new window

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)

Open in new window

Then In F1 I will have Data Validation List with below formula:
=UniqueYears

Open in new window

Then finally in Column E I will have Count with below formula:
=IF(A2="","",COUNTIFS($D:$D,"Yes",$B:$B,$F$1))

Open in new window

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 you
You're welcome.
No you don't need to confirm this formula with Ctrl+Shift+Enter as Sumproduct can handle array calculation.