reconciling countif?????

[esquared8]

I'm head deep in reconciling countif formula... I have a spreadsheet containing 465 rows of data. Each question is tied to a subtopic. I've used a countif formula to count how many times I'm using all of the topics. I come up with 491 uses of all subtopics. How can this be if I only have 465 questions? Each question is only tied to 1 subtopic.

I've checked every countif formula and it is indeed giving me the correct number. ex: aprs is used 3 times, pricing request is used 13 times, etc. they are all correct.

Why won't the total number of questions match the total number of times a subtopic is used?

 

[jmthompson]

I think we'd need to see at least some of the file to help solve this one

 

[esquared8]

questions:
need a payment calculator
looking for product eligibility
need brand collateral

topics:
APRs
program exception
collateral request

on a separate tab I have:
APRs - 3
program exception - 40
collateral request - 0

In all I have 74 topics.

does this help?

 

[jmthompson]

What do your formulas look like?

Are all of your questions and topics in the same column?

 

[Kaps_mr2]

I'm guessing out of the blue .... but have you locked all your ranges i.e used $ to ensure that your range is fixed and doesn't vary from row to row. Thanks

Kaps

 

[esquared8]

=COUNTIF(abcd_tracking_log!$F$2:$F$466,"APRs")

the questions and topics are in separate columns. The countif formula is on another spreadsheet within the worksheet.

 

[jmthompson]

To recap,

you have a spreadsheet with 465 questions each with one subtopic, so you have 465 subtopic lines (74 unique sub-topics)

on a separate tab, you have a countif formula for each of the 74 sub-topics

the countif results are correct

you have summed the results of all of the countif formulas, the result of this sum is greater than 465.

Correct?

 

[esquared8]

yes, that is exactly what is happening. I don't understand why it doesn't reconcile with 465.

 

[jmthompson]

Hard to say without seeing your file. Are there any hidden rows in your abcd tracking log that might contain instances of your subtopics?

How did you verify the counts of your subtopics?

 

[esquared8]

I filtered for each subtopic and counted manually.

I also checked for hidden rows and actually deleted every row after 466 just to be sure. No luck...