# Subtotal / Countif ?

#### baillieston

##### Board Regular
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

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
baillieston said:
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))

Hi,

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

And my task will be much easier now. Thanks again

Replies
9
Views
376
Replies
1
Views
442
Replies
3
Views
93
Replies
0
Views
274
Replies
1
Views
121

1,203,529
Messages
6,055,933
Members
444,836
Latest member
nVaNL

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back