Subtotal / Countif ?

[baillieston]

Hi,
I have a filtered list containing many colums.
One of the columns is "Gender" and can be either M or F

I know that I can use the Subtotal function with a 3 to show me the total of what I filter on. Also I know that I can use the Countif function to show me only the total of what I specify.

Can I combine these functions ?

I have another column called status which can be either Sent or returned.
I want a some cells to show the count of the Ms and Fs when the status is returned and another 2 to show the total Ms and Fs. I want to use these number for some charts that update as I update the status.

Any help would be appreciated.
Thanks

 

[Aladin Akyurek]

Hi,
I have a filtered list containing many colums.
One of the columns is "Gender" and can be either M or F

I know that I can use the Subtotal function with a 3 to show me the total of what I filter on. Also I know that I can use the Countif function to show me only the total of what I specify.

Can I combine these functions ?

=SUMPRODUCT(SUBTOTAL(3,OFFSET(GenderRange,ROW(GenderRange)-MIN(ROW(GenderRange)),,1)),--(GenderRange="M"))

would calculate a conditional count with Gender = M, while AutoFilter activated.

I have another column called status which can be either Sent or returned.
I want a some cells to show the count of the Ms and Fs when the status is returned and another 2 to show the total Ms and Fs. I want to use these number for some charts that update as I update the status.

...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(GenderRange,ROW(GanderRange)-MIN(ROW(GenderRange)),,1)),--(GenderRange="M"),--(StatusRange=Status))

 

[baillieston]

Hi,

Thanks very much for your quick response. This seems to work fine.

And my task will be much easier now. Thanks again