problem with =COUNTIF((OFFSET(INDIRECT(CONCATENATE(("'Quality Audit Criteria'!$f$",MATCH(

[nadiac2402]

I am using this nested function: =COUNTIF((OFFSET(INDIRECT(CONCATENATE("'Quality Audit Criteria'!$f$",MATCH($B12,'Quality Audit Criteria'!$F:$F,0))),0,1,COUNTIF('Quality Audit Criteria'!$F:$F,$B12),1)),F$11)

This is used in the table on the first sheet. It references data in the "Quality Audit Criteria" sheet including values in a List Box. The count is correct for rows 13 to 16 in the table, but row 12 does not add up.

I have a feeling the List Box is causing the problem - however, I'm quite sure there is probably a better way to do this.

I have attached the spreadsheet.

Does anyone have any idea why this does not work for row 12 but works fine for the other rows, or any other suggestions please??

Thank you
Nadiac

[nadiac2402]

correction it references Data Validation List not List Box.

[vlady]

you have blanks....
without altering your formulas i came up with this.

Please Login or Register  to view this content.

i just added a count of blank in the given range of the offset function

EDIT: added shorter version i think

another one... change your countif

Please Login or Register  to view this content.

[nadiac2402]

Thank you. Both formula work in row 12 of the table but not the other rows but I will work that out.

thanks again.

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.