quantitative anomaly-- inconsistent values, help me find formula error!

I am currently working with the results of a study-- on a deadline. I am calculating how many participants selected each gender.

So first, I determined how many responses were submitted. ONLY rows 2-735 are responses, which means that 734 responses were submitted.

Then I wrote my formulas:

Responded female:
=SUM(LEN(C1:C735)-LEN(SUBSTITUTE(C1:C735,"Female","")))/LEN("Female")

Responded male: (same, but with "Male")

Male to Female Transsexual or Transgender

Female to Male Transsexual or Transgender

Preferred Not to Respond


I also calculated for the same range =countblank(C1:C735) because I wanted to "check my work."

When I calculated the final sum of all responses + blank cells looking for a total of 734, I got 738. I went in and double checked all of my formulas, but they're all good. Ranges are correct. This tells me that I have an error somewhere, because obviously 738 is a bigger number than 734, and I don't know where those 4 extra results are coming from. I also took the liberty to go through ALL of this data and count the blanks, and there were, indeed, 9.

I realize this is wordy, so I've attached the dataset with formulas for reference.

HELP!
(and thank you in advance).

According to the forumrules, please change the title of your question.

@mtmorce

You don't change the title of your question.

In that case we are not allowed to respond on your question.

on the row 753 and 754 col B try this formula...

Please Login or Register  to view this content.

I cant remember if 2007 has COUNTIF

see the attached file, with pivot table.