# basic countif function

1. ## basic countif function

hey guys

having a brain malfunction today

i need a simple formula, where it looks at the name col, and counts how many time, each status showed up under that name

for eg mason had 3 live status
fred had 3 qualfied
danielle 3 sent
3 risk
3live

i no this is basic question so i appreciate your helpweeknum.xlsx

2. ## Re: basic countif function

Hi

In D4 and copy down.

=SUMPRODUCT((\$B\$4:\$B\$18=B4)*(\$C\$4:\$F\$4=C4))

Is this works,for you?

3. ## Re: basic countif function

thank you for your response. greece is a lovely country, i have this friend in patra

for some reason i cant get tht formula to work

4. ## Re: basic countif function

How about a Pivot table.

Drag the Name and Status to Row Labels, then the Status dragged again to Summation area...

5. ## Re: basic countif function

hey nbvc i have over 60,000 records, and already built a summary sheet, so i need to have a formula

6. ## Re: basic countif function

.."Sound" strange to me..

Pls, take a look to the example.

So you have a Greek(?) friend in Patra?

Have you ever visit Greece?

7. ## Re: basic countif function

Then you would need COUNTIFS

e.g.

=COUNTIFS(Sheet2!B:B,A2,Sheet2!C:C,B2)

where A2 and B2 contain a name and status respectively... copy formula down.

8. ## Re: basic countif function

hmm , okay. i play around with it

9. ## Re: basic countif function

Ok....I really don't know.. Apologize..

This is the corect formula.

=SUMPRODUCT((\$B\$4:\$B\$18=B4)*(\$C\$4:\$C\$18=C4))

Copy down.
That's why formula does not works for you...

10. ## Re: basic countif function

guys unless i am being super thick, but i am pretty sure the formula should be

=COUNTIFS(B4:B18,B4,C4:C18,C4)
b4:b18 is looking at the range, b4 is selecting who i want, then c4:c18 is looking at the status, and c4 is the specfic status. and that gives me the total. thats the correct formulas i belive

11. ## Re: basic countif function

Fotis check private msgs

12. ## Re: basic countif function

Yes. Corect. I am sure that NBVC didn't noticed that your data starts in Row 4...

As you are in Excel2007, this is better solution than SUMPRODUCT(The second one) that also works.

13. ## Re: basic countif function

Originally Posted by masond3
guys unless i am being super thick, but i am pretty sure the formula should be

=COUNTIFS(B4:B18,B4,C4:C18,C4)
b4:b18 is looking at the range, b4 is selecting who i want, then c4:c18 is looking at the status, and c4 is the specfic status. and that gives me the total. thats the correct formulas i belive
That is similar to what I have suggested.. except I assumed your summary was in a separate sheet....

If not, then

=COUNTIFS(\$B\$4:\$B\$18,B4,\$C\$4:\$C\$18,C4)

copied down...

14. ## Re: basic countif function

NBVC i no , i was having a blonde moment, thanks for your help guys

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1