mark hansen
Well-known Member
- Joined
- Mar 6, 2006
- Messages
- 534
- Office Version
- 2016
- Platform
- Windows
I have a list of column headers and I need to count the amount of times "No" shows up in a column for a given date range and person name. I have this formula, which works well.
=COUNTIFS(Table1[Date of Visit],">="&$AY$32,Table1[Date of Visit],"<="&$AY$33,Table1[Nurse name],$BA$31,Table1[Pediatric Question 1],"No")
The "Pediatric Question 1" is the header name as well as the label name to indicate which column the count is for. How would I change " Table1[Pediatric Question 1],"No" " so the column header name is picked up from the same name used as a label name. In my searches for this, I have found, " =MATCH($AY35,Table1[#Headers],0) " which returns the number 7, which is the column number for Pediatric Question 1. (AY35 is where the column label name is.)
How can I incorporate the match statement to indicate the column to count the word "No" in the column with the header in AY35?
I can go down the list of column header labels changing the formula to the new column headers, but there should be a better way.
Thanks for any ideas.
Mark
=COUNTIFS(Table1[Date of Visit],">="&$AY$32,Table1[Date of Visit],"<="&$AY$33,Table1[Nurse name],$BA$31,Table1[Pediatric Question 1],"No")
The "Pediatric Question 1" is the header name as well as the label name to indicate which column the count is for. How would I change " Table1[Pediatric Question 1],"No" " so the column header name is picked up from the same name used as a label name. In my searches for this, I have found, " =MATCH($AY35,Table1[#Headers],0) " which returns the number 7, which is the column number for Pediatric Question 1. (AY35 is where the column label name is.)
How can I incorporate the match statement to indicate the column to count the word "No" in the column with the header in AY35?
I can go down the list of column header labels changing the formula to the new column headers, but there should be a better way.
Thanks for any ideas.
Mark