CountIfs Function

[GN0001]

I have a Countifs Function:

=COUNTIFS($K$2:$K$396,$K$2,$L$2:$L$396,$L$2,$I$2:$I$396,$I$2)


I get the answer. But How can I cross check this function to make sure it has retrieved the correct count when the table has thousands of Rows?


Do we need to cross check when we use Sumifs or Countif?


I am putting some sample of rows here extending I to L:


UU UU1 73300 $10.67

UU UU2 83530 $31.20

UU UU3 44470 $10.08

UU UU4 91518 $26.08

UU UU5 91300 $21.34

UU UU6 44540 $10.08

UU UU7 73280 $10.67

UU UU8 83690 $17.20

UU UU9 91408 $21.34

UU UU10 44580 $10.08

UU UU11 91331 $22.53

UU UU12 83910 $23.12

AA AA1 73260 $13.52

UU UU13 60310 $11.86


High Regards,

Guity

 

[Hui]

Guity

I am always a fan of cross checking, not because you should have to cross check the functions answers (but that doesn't hurt), but because you should be cross checking the logic of your model.


If there are no duplicates, The sum of the column with your formula should be equal to the number of rows and the maximum value of that column should be 1


If there are duplicates, the maximum will be greater than 1 and will appear multiple times


I would be looking at checking the sum of the values or some other technique.

 

[GN0001]

Hui,


1)If I have a table regardless of number of rows, I filter the rows which I need them to be in my table. Then I compare the total of filtered rows with the total of my formula result. Do you use the same technique?


2-You would be saying that another way of cross checking is to check for duplicates when we expect only single values. if there are no duplicates then the maximum value of that column should be 1: I understand this and I utilized this function below for this purpose.

=MAX(COUNTIF($E$6:$E$10,$E6))


May I ask What would be the function/ technique you use for this purpose?


What other techniques would you use for cross checking in addition to summing of the values and finding duplicates?


Thank you for all your help.


Regards,

Guity

 

[Hui]

Guity


There is no definitive guide to how to validate data as each case is different based on the data


But as a Guide and as applicable


Check for duplicate values/codes

Check for Min not less than 0

Check for Max

Check for values <> int(value)

Check that sum(sub totals) = sum(individual results)

Check that sum (distributed values) = sum(original values)

Check Sum(Page Totals) = sum(page individual results)

etc


You probably get the idea


Sounds like a good idea for a future post...

 

[GN0001]

Hui


Thank you for the help and I got the idea.


Guity