Perplexing Excel question

I have a question that involves counting. I have a list of job requirements in column B and a checking column in Column A (I type the letter "a" and a check mark appears). I use this to check off which requirements have been completed (there are 266). There are 2 types of job requirements: Core Tasks denoted by a "#" and regular tasks non denoted. I have set up Autofilters for both the "checks" column and the Job requirements column to easily sort through core tasks, regular tasks, both, and which ones have/have not been completed.

My question is: I would like a formula that automatically counts how many requirements I have listed at any given time. I tried using various =COUNTIF formulas but I can't seem to t get it to work.

Any suggestions? I've also tried to provide a snipit of my actual excel page for you to look at.

HI

How about something like

core completed:

=sumproduct(--(a2:a500="a"),--(left(b2:b500,1)="#"))

Non core completed:

=sumproduct(--(a2:a500="a"),--(left(b2:b500,1) <> "#"))

Adjust ranges as required.

hth

rylo

That doesn't seem to work but it is definately on the right track. The values in the cell show as "0" for both equations, no matter how I adjust the Autofilters. Any guess on how to fix that?

Well, I've gotten the to produce values...but the values still don't change as I change the autofilter. Any help would be appreciated...

Hi

The numbers won't change based on the autofilters as they give the totals for that combination for the entire range.

You could adapt the formulas for the other combinations (Core not completed, non core not completed)

However, if there is some other criteria that you are using for the filters, what is it???


rylo