Log In
Register
Home
December 4, 2021
Offline
I need to find a way to count multiple criteria including blank cells. I have attempted COUNTIFS and SUMPRODUCT, but cannot get them to work. My data file has a total of 1,005 records. I must determine what is the count that meets ALL of this criteria.
- tbl_data[Portfolio]=""
- tbl_data[Disposition]=""
- tbl_data[Disposition]="Keep Requires Edit"
- tbl_data[Disposition]="Potential Enterprise"
- tbl_data[Disposition]="Potential Obsolete"
I will then do another count by switching the 1st item from what is list to tbl_data[Portfolio]="Cardiovascular". This is a metrics table that I am working on, so the formula will be adjusted for each of the other cells slightly. I just cannot figure out how to make it work when BOTH blank cells and other criteria are required. Thanks in advance. Please not, there are no numbers to be counted, only the total # of times that each of the criteria is displayed.
Update: Okay, I was able to figure out PART of what I needed. This formula provides me with a count of 13, however it also includes a Status of "Not in Scope" I do not want. I am not sure how it captures this status as I have other statuses within my dataset. Any Ideas? Thanks again!
=COUNTIFS(tbl_data[Portfolio],"Cardiovascular")+COUNTIFS(tbl_data[Disposition],"",tbl_data[Disposition],"Keep Requires Edit",tbl_data[Disposition],"Potential Enterprise",tbl_data[Disposition],"Potential Obsolete",tbl_data[Disposition],"")
VIP
Trusted Members
December 7, 2016
Offline
Hello,
See attached example file for a slightly different approach. I can't say why you get the Not in Scope data in count, but you do have tbl_data[Disposition],"" twice in your updated formula, which makes it count the matching data twice.
If you still don't get the expected result, please upload a sample file so we can have a look of what might be causing the error.
Br,
Anders
1 Guest(s)
