having difficulties in Countif

[oasalako1]

Hi,

I am trying to use Countif just as Sumif buit it doesn't help.

I want to count the number of times each of the names appear as in my attached spreadsheet example.

Please any help would be apprecaited. I have attached a spreadsheet to explain my self better.

Many thanks

OCountif and Sumif.xls

[brokenbiscuits]

=COUNTIF($B$5:$B$16,B21)

or maybe

=SUMPRODUCT(--($D$5:$D$16>0),--($B$5:$B$16=B21))

[Sam Capricci]

It looks to me like you want =SUMIF($B$5:$B$16,B21,$C$5:$C$16), and =COUNTIF(B5:B16,B21) to get the sum of "Baby" and the count of "Baby".

EDIT: BTW, this would give you both the count and sum in the same cell...
=COUNTIF($B$5:$B$16,B21)&" - "&SUMIF($B$5:$B$16,B21,$C$5:$C$16)

[oasalako1]

Hi

Thanks for your responce, but this has not worked for me.

I want to be able to count the number of e.g Baby, Factory above for Each month (Jan to Dec).
I can sum, but not able to count by each of the months which is what i want to do

Hope this help. Pls see attached spreadsheet.
O

[Sam Capricci]

so if i understand it correctly, you have Baby listed three times and there are 12 months so the total should be 36 (3x12) right? Is that the formula you need? (and i'm not trying to minimize it because you just gave a sample of your data.)
And if this is correct, is it in a list that can be longer than 12 months? Or put differently, would a formula that counts the # of times Baby is found in col B then simply multiplies that value by 12 not work?

EDIT: if above it true wouldn't this work?
=COUNTIF($B$5:$B$16,B21)*12

[oasalako1]

Thanks for your responce, but I need a formula that counts the # of times Baby is found in each of col C-N i.e Jan to Dec.

Pls kindly assume that listing on Col "B" is about 1000 line items and I need to how many times each month they were paid.

Hope this clarifies. Many Thanks for your help

O

[martindwilson]

fill in that sheet with the answers you expect

[oasalako1]

Hi All,

I have filled in the sheet with answer i expect to see.

Hope this help. many thanks

O

[martindwilson]

in c29 filled across and down
=SUMPRODUCT(($B$7:$B$23=$B29)*(C$7:C$23<>""))

[oasalako1]

Excellent martin Wilson, This has worked. You are wonderful. Thank to you

Many thank to Sambo Kid and BrokenBuiscuits too.

Thank you all

[abousetta]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.