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!

[TABLE="class: grid, width: 300"]
<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1561[/TD]
[TD]Bob[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]2206[/TD]
[TD]Bob[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]200399[/TD]
[TD]Bob[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]1914[/TD]
[TD]Sue[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]1913[/TD]
[TD]Sue[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]2102[/TD]
[TD]James[/TD]
[TD]93[/TD]
[/TR]
[TR]
[TD]985[/TD]
[TD]James[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]1989[/TD]
[TD]James[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]Chelsea[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.

[TABLE="class: cms_table_grid, width: 300"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1561[/TD]
[TD]Bob[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]2206[/TD]
[TD]Bob[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]200399[/TD]
[TD]Bob[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]1914[/TD]
[TD]Sue[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]1913[/TD]
[TD]Sue[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]2102[/TD]
[TD]James[/TD]
[TD]93[/TD]
[/TR]
[TR]
[TD]985[/TD]
[TD]James[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]1989[/TD]
[TD]James[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]Chelsea[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]C=0[/TD]
[TD]C >0[/TD]
[/TR]
[TR]
[TD]Count[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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

[TABLE="class: cms_table_cms_table_grid, width: 300"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1561[/TD]
[TD]Bob[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]2206[/TD]
[TD]Bob[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]200399[/TD]
[TD]Bob[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]1914[/TD]
[TD]Sue[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]1913[/TD]
[TD]Sue[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]2102[/TD]
[TD]James[/TD]
[TD]93[/TD]
[/TR]
[TR]
[TD]985[/TD]
[TD]James[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]1989[/TD]
[TD]James[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]701[/TD]
[TD]Chelsea[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]




[TABLE="class: cms_table_grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD]C=0[/TD]
[TD]C > 0[/TD]
[/TR]
[TR]
[TD]Count[/TD]
[TD]1[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

 

[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")