Mar 16 2018 03:04 PM
Mar 16 2018 10:22 PM
Hi,
Based on the example you attached, you can count the number of injured players by counting the number of occurrences for each category!
You can use this formula:
=COUNTIF($A$2:$A$11,E3)
You can also use pivot tables to summarize the data.
Please find the attached file.
Mar 16 2018 10:27 PM - edited Mar 16 2018 10:29 PM
Vicente,
insert a pivot table and add it to the data model.
category in rows and name in values.
Change the value field settings for name to Distinct Count.
Edit:
One more thing.
Blank rows in a data set are considered bat data. Remove the blank rows.
Mar 17 2018 05:18 AM
Mar 17 2018 05:55 AM
Hello Vicente,
I've updated the formula to take into account the issue you mentioned as follows:
=SUM(IF(COUNTIFS($A$2:$A$11,E3,$B$2:$B$11,$B$2:$B$11)>1,0.5,COUNTIFS($A$2:$A$11,E3,$B$2:$B$11,$B$2:$B$11)))
But to enter this formula, you have to press Ctrl+Shift+Enter at the same time to force it to deliver the correct result because it's an array formula.
Please find the attached file.
Mar 17 2018 03:39 PM
SolutionMar 17 2018 03:46 PM
Mar 18 2018 12:45 AM
@Vicente Heyermann Vial wrote:
somewhere I read that I need to tick the "Add this data to the Data Model" option when creating the pivot Table, but it's not available for me.
You didn't look hard enough.
Mar 18 2018 10:17 AM
Mar 18 2018 11:18 PM
Does this solve your issue?
Mar 17 2018 03:39 PM
Solution