Using SumIFs within CountIFs

[FeliciaMD]

Hello - for simple use by other users, I'm limited to standard Excel 2010 functionality.

I'm trying to do a count of A, where sum of C by unique B are >0. This would be the first step of a much bigger CountIFS.

HELP!

A	B	C
1561	Bob	35
2206	Bob	-
200399	Bob	-
1914	Sue	28
1913	Sue	-
2102	James	93
985	James	-
1989	James	-
701	Chelsea	-

<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[Gerald Higgins]

Hi, welcome to the board.

I can't follow how you get those results from your sample data.

Can you explain please ?

 

[FeliciaMD]

Hi, welcome to the board.

I can't follow how you get those results from your sample data.

Can you explain please ?

This is the given data - I'm trying to do a count that will go into another table.

Apologies.

A	B	C
1561	Bob	35
2206	Bob	-
200399	Bob	-
1914	Sue	28
1913	Sue	-
2102	James	93
985	James	-
1989	James	-
701	Chelsea	-

<tbody>
</tbody>

	C=0	C >0
Count

<tbody>
</tbody>

 

[FeliciaMD]

Apologies - trigger happy today.

I'm trying to do a count of A, where sum of C by unique B are >0. This would be the first step of a much bigger CountIFS

A	B	C
1561	Bob	35
2206	Bob	-
200399	Bob	-
1914	Sue	28
1913	Sue	-
2102	James	93
985	James	-
1989	James	-
701	Chelsea	-

<tbody>
</tbody>

	C=0	C > 0
Count	1	8

<tbody>
</tbody>

 

[Gerald Higgins]

That doesn't make it any clearer to me I'm afraid.

How do you get a result of 1, for C=0 ?
How do you get a result of 8, for C > 0 ?

 

[RoryA]

I'd just use a helper column (say D) that uses =SUMIF(B:B,B2,C:C) then use that column for a COUNTIF(D:D,">0")

 

[FeliciaMD]

That is the manual count, that I'm trying to achieve with a formula.

I have 9 contracts (A), 3 belonging to Bob(B), 2 belonging to Sue(B), 3 belonging to James(B), and 1 belonging to Chelsea(B).
I have to count the contracts(A) by their grouped income (C).

That doesn't make it any clearer to me I'm afraid.

How do you get a result of 1, for C=0 ?
How do you get a result of 8, for C > 0 ?

 

[Gerald Higgins]

Well perhaps I'm being thick but I still don't get it.

How do you get 1 ?
How do you get 8 ?

 

[Noodleski]

Well perhaps I'm being thick but I still don't get it.

How do you get 1 ?
How do you get 8 ?

I think Chelsea is the one and the others combined are eight. Asfaics somebody from the B column has at least one value, it belongs in the C>0 count. It's only when there's someone with a zero over all lines they go into C = 0 count.
And if I'm right, both of these counts should add up to the number of rows, making one formula a very easy row numbers - count.

 

[FeliciaMD]

Thanks, I was trying to avoid it, but think this will be simplest for now. Nothing like holding up analysis because you can't get your formula to work. :/

I'd just use a helper column (say D) that uses =SUMIF(B:B,B2,C:C) then use that column for a COUNTIF(D:D,">0")