SUMIF but need to count rather than sum

Hi,

Can anyone helpt with this please. I cannot seem to find a count function that will work the same as a sumif but counting the entries rather than summing them.

For example

Column A list of product types sold
Column B the value of products sold

Sumif will look at column B and find any value that has the same product type in column A and add them up. I need to do the same but counting them instead.

Any help much appreaciated.

Pat

Hi. The countif works differenty to the sumif function - I need the exact same function of sumif but counting rather than adding up.

If I give a better example of what I need perhaps

A B
Client 1 10
Client 2 5
Client 1 15
Client 3 6
Clinet 1 20
Client 4 2
Client 4 5

Sum if would tell me Client 1 spent 45, the sum of all values in column B where column A was equal to client 1.

I want the formula that tells me that the number of times Client 1 ordered was in this example 3.

I dont think countif achieves this

?

Thanks DonkeyOte but this still doesnt solve the problem.

Using my example, the table I want the results in will be similar to one with the data in although one line for each client number. It will expand right for each weeks product sales, so

A B C
Client 1 10 12
Client 2 5 2
Client 1 15 -
Client 3 6 5
Clinet 1 20 30
Client 4 2 4
Client 4 5 1

So the count function I am looking for will list Clients' 1-4 in column A (but only once) with colum B showing the number of orders sold in week 1 and in C number sold for week 2 and so on, so the result table will look something like

A B C
Client 1 3 2 (as the third line down in my example did not have an order in week 2)
Client 2
Client 3
Client 4

I know this is hard to read as doesnt show in columns - not sure if I can attach an example spreadsheet to show you more clearly?

Originally Posted by pat brown

Thanks DonkeyOte but this still doesnt solve the problem.

Using my example, the table I want the results in will be similar to one with the data in although one line for each client number. It will expand right for each weeks product sales, so

A B C
Client 1 10 12
Client 2 5 2
Client 1 15 -
Client 3 6 5
Clinet 1 20 30
Client 4 2 4
Client 4 5 1

So the count function I am looking for will list Clients' 1-4 in column A (but only once) with colum B showing the number of orders sold in week 1 and in C number sold for week 2 and so on, so the result table will look something like

A B C
Client 1 3 2 (as the third line down in my example did not have an order in week 2)
Client 2
Client 3
Client 4

I know this is hard to read as doesnt show in columns - not sure if I can attach an example spreadsheet to show you more clearly?

Hi,

Assuming titles in Row 1, data in rows 2:13 then

in (say) Cell B20

=CountIf(A$2:A$13,"Client "&Row()-19) will count the occurances that match the said client

This first fomula can be duplicated downwards and will reflect the count for the items in the rows concerned (hence Client 1 in the first Row, client 4 in the 4th.

and in (say) cell C20

=Sumproduct(--($A$2:$A$13="Client "&Row()-19),(--(C$2:C$13<>"")))

can be used on Row 20 (cell C20) and then Formula copied rightwards and downwards etc.

Regards

DunnyDoor

Thanks guys. The data that will be driving the result will constantly change and be added to in both rows and columns for each particular week.

Attached is a spreadsheet with the base data, the data with SUMIF and underneath that the result that I want to find formula for, which is basically exactly the same as SUMIF but rather than adding values, returning the number of entries.

I am using Excel 2007

Hope this helps and thanks in advance.

Pat

DonkeyOte

The COUNTIFS function has solved this question.

Thankyou to all for the input. Will mark this as solved now.

Pat