I've transformed the dataset with power query and loaded it into Excel. In one column values are both text and numbers. In power query, this shows as text & number. When I transform it into excel data is only stored as text. I want to get the count from that column based on multiple criteria. I used COUNTIFS function to do that as below.
=COUNTIFS(Yearly_Data[Medical Year],Processing!$B$32,Yearly_Data[Attribute],Processing!$A$34,Yearly_Data[Value],"<"&70,Yearly_Data[Value],">"&100)
Due to data stored as text, it returns 0, but there must be a value greater than 0. Also, I tested the dynamic array which is converted to numeric values, within the COUNTIFS function as below.
=COUNTIFS(VALUE(FILTER(Yearly_Data[Value],Yearly_Data[Attribute]=Processing!$A$34,"")),">100")
It returns an error. I am using Office 365 latest. I am trying to get the number of values greater than 100. Can anybody give an idea to achieve this task?
this function is to get the number of values>100 for blood sugar.
my values related to tests can be discovered from the above image of the table. in the values column data is stored as the text & I can not convert it within the table due to both text and numbers contained within that column.
Countifs works that all the criterias must be met. In your formula you want to get less then 70 and greater then 100 which always returns 0.
Therefore try to use to sum the 2 criterias something like this:
If texts are in the value cells then try this formula