Countif help

[swmwshrk]

I can't get my formula to work

To make it simple I need to total the number of times the letter C appears next to each level (Cells D20,D21,D22,D23,D37,D38,D39,D40). countif.xls

The only things that will be in these cells are
~ no work done
P partially complete
C Completed.

There will be no C in any other cell in column D.

[Palmetto]

You need to remove the merged cell and user center across selection instead.

=COUNTIF(D20:D40,"C")

Or if you really must have the merged cell

=COUNTIF(D20:D23:D37:D40,"C")

[swmwshrk]

Palmetto

Thanks for your response. That only gives me the total # of c's. I need the total c's by level
Level1 total (D20&D37)
Level2 total (D21&D38)
Level3 Total (D22&D39)
Level4 Tota (D23&D40)

Remember what I uploaded is just a sample. The actual sheet has over 500 rows, so I am looking for something that tells excel to total up the C's when they appear next to a specific level.

[Palmetto]

Placed in cell D3:

=COUNTIFS($B$8:$B$40,"Level 1*",$D$8:$D$40,"C")

Unmerge your "Activity X" cells and use center across selection, then you can refer to the range of cells without a problem.

Adjust the number (i.e. 1) in the formula to read Level 2, etc. for each of the levels

[Cutter]

COUNTIFS() is not available in pre 2007 versions.

@swmwshrk

If you are using 2003 like your profile & file type suggest, then:

=SUMPRODUCT((LEFT($B$20:$C$40,7)=LEFT(B3,7))*($D$20:$D$40="C"))

[swmwshrk]

I am using 2003. I don't understand your formula, and aI can't get it to work.

What does Left mean?
Whats with the 7?
Why the (( after sumproduct?

I am fairly new to complex formulas in excel. Please give me the formula exactly as it would be entered in the worksheet. Thanks you for helping.

[Cutter]

That is the formula exactly as it would be in the worksheet.

Copy it from my post and, with cell D3 selected, paste it into the formula bar and hit Enter.
Now drag it down to cell D6.
The results in D3:D6 will be 0,2,2,1

The formula is counting how many times "C" is in column D where the first 7 characters in column B match the first 7 characters in B3, B4, B5 & B6 respectively.

So in cell D3 it's looking for "Level 1" (the first 7 characters), in cell D4 it's looking for "Level 2", etc.

The (( after SUMPRODUCT: The first is for the SUMPRODUCT function itself. The second is wrapping the LEFT($B$20:$C$40,7)=LEFT(B3,7) portion.
The 2 portions of the SUMPRODUCT return TRUE/FALSE results. By using the * it transforms those results to their numeric 1/0 equivalents.
Those equivalent numeric values are then summed and produce the count.

[swmwshrk]

Thank you so much for the formula and for explaining.

[Cutter]

You're welcome. If you are satisfied with the solution(s) provided, please remember to mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[swmwshrk]

I was just putting the formual into the 125 cells I needed it in, making sure it worked before I did so.

[Cutter]

Great. Thanks for the 'star tap'.