How to use countif with subtotal

I have a filter on a result list with different products and dates associated how could I do this

=Subtotal(COUNTIF(Columndate,C3))

I see that countif is not an option with subtotal

 

thanks in advance for your help

 


Neall

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

I have a filter on a result list with different products and dates associated how could I do this

=Subtotal(COUNTIF(Columndate,C3))

I see that countif is not an option with subtotal

 

thanks in advance for your help


Try this...

The full unfiltered range is B2:B11

You want to count how many cells contain the word "Yes" when the range is filtered (or unfiltered).

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B11,ROW(B2:B11)-ROW(B2),0,1)),--(B2:B11="Yes"))

--
Biff
Microsoft Excel MVP

Biff
Microsoft Excel MVP

KISS - Keep It Simple Stupid

168 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

If your filtered dates are in Col_C, beginning in Row_2...
Assuming you have 30 items listed (before filtering)
and F1: (a date to match in the filtered list....eg 1/1/2010)

This regular formula returns the count of visible Col_C dates
that match the date in cell F1

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C2,ROW(2:30)-2,))*(C2:C30=F1))

Is that something you can work with?


Ron Coderre
Microsoft MVP (2006 - 2010) - Excel

P.S. If any post answers your question, please mark it as the Answer (so it won't keep showing as an open item.)
Regards,

Ron Coderre
Former Microsoft MVP - Excel

14 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

The full unfiltered range is B2:B11

You want to count how many cells contain the word "Yes" when the range is filtered (or unfiltered).

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B11,ROW(B2:B11)-ROW(B2),0,1)),--(B2:B11="Yes"))


Wouldn't the first argument for the SUBTOTAL function be 103 instead of 3 for the filtered data?

=SUMPRODUCT(SUBTOTAL(103,OFFSET(B2:B11,ROW(B2:B11)-ROW(B2),0,1)),--(B2:B11="Yes"))


NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.
*** PLEASE READ ***

If this response answers your question, please mark it as the "Answer".

15 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

The full unfiltered range is B2:B11

You want to count how many cells contain the word "Yes" when the range is filtered (or unfiltered).

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B11,ROW(B2:B11)-ROW(B2),0,1)),--(B2:B11="Yes"))


Wouldn't the first argument for the SUBTOTAL function be 103 instead of 3 for the filtered data?

=SUMPRODUCT(SUBTOTAL(103,OFFSET(B2:B11,ROW(B2:B11)-ROW(B2),0,1)),--(B2:B11="Yes"))

The 100 series arguments will handle both hidden and filtered rows.

The OP didn't mention hidden rows, only: I have a filter on a result list.

So, I guess either one one will work since they're using Excel 2007.

I use Excel 2002 as my primary spreadsheet app and Excel 2002 doesn't have the 100 series arguments for SUBTOTAL. As a matter of habit, I don't use the 100 series arguments unless I'm working in Excel 2007 and I need to use the 100 series arguments.

--
Biff
Microsoft Excel MVP

Biff
Microsoft Excel MVP

KISS - Keep It Simple Stupid

6 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

>> Wouldn't the first argument for the SUBTOTAL function be 103 instead of 3 for the filtered data?
>>
>> =SUMPRODUCT(SUBTOTAL(103,OFFSET(B2:B11,ROW(B2:B11)-ROW(B2),0,1)),--(B2:B11="Yes"))

The 100 series arguments will handle both hidden and filtered rows.

The OP didn't mention hidden rows, only: I have a filter on a result list.

@Biff,

I should have mentioned in my reply that I assumed the OP meant some rows containing the dates in C3 (that he was looking for) were hidden because I assumed he filtered on a different column when he said his data was filtered and that his attempted formula seemed to indicate he wanted to count only the visible matches. I now see Ron ended up proposing a formula with SUBTOTAL using 3 as the first argument as well, so maybe my assumptions are incorrect.


NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.
*** PLEASE READ ***

If this response answers your question, please mark it as the "Answer".

2 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

So just for clarity I want to see how many customers submitted a request on date X but the customer could of called on against product A,B,C.

 

the plan was just to filter on  Products then it would give me a customer list and the dates they entered a request, I then wanted to get a count on the number of times they submitted a request each month using the countif against Jan, Feb, March.....

 

 

Thanks


Neall
NH

2 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

You can get your counts without filtering your data first. The following formula will work with or without filtering your data... since you didn't give us your layout, I will assume your Customers are listed in Column A, the Products they bought are in Column B and the date they bought the product is in Column C. I will use hard coded customer name (Customer A), product name (Product A) and month of interest (7 for July), but you can put these values in cells and then use the cell address in place of the hard coded values in this formula.

Count of "Customer A" purchased in July...
-----------------------------------------------------------------------------------
=SUMPRODUCT((A2:A1000="Customer A")*(MONTH(C2:C1000)=7))

Count of "Customer A" purchases of "Product A" in July
-----------------------------------------------------------------------------------
=SUMPRODUCT((A2:A1000="Customer A")*(B2:B1000="Product A")*(MONTH(C2:C1000)=7))

Count of "Customer A" purchases of "Product A" for all months
-----------------------------------------------------------------------------------
=SUMPRODUCT((A2:A1000="Customer A")*(B2:B1000="Product A"))

You can change the ranges as necessary, but there must be the same number of cells in each range used.


NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.
*** PLEASE READ ***

If this response answers your question, please mark it as the "Answer".

2 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

So just for clarity I want to see how many customers submitted a request on date X but the customer could of called on against product A,B,C.

 

the plan was just to filter on  Products then it would give me a customer list and the dates they entered a request, I then wanted to get a count on the number of times they submitted a request each month using the countif against Jan, Feb, March.....

Well, I'm not sure what you're trying to do.

If possible, it would help if you could post some sample data and then explain what result you expect.

--
Biff
Microsoft Excel MVP

Biff
Microsoft Excel MVP

KISS - Keep It Simple Stupid

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Just to follow up on my previous message, if you are looking for a specific date rather than a month, just remove the MONTH function and test for the date instead. Using the first formula I posted, this is what it would look like if you want to count the number of orders placed by Customer A on 7/15/2010 no matter which product they ordered...

=SUMPRODUCT((A2:A1000="Customer A")*(C2:C1000=DATE(2010,7,15))
NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.
*** PLEASE READ ***

If this response answers your question, please mark it as the "Answer".

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

OK let me try and clean this up a bit

 

I have a result list that shows me (A1:A3000) customer names another (B1:B3000) that shows me what products they opened the issue againsts and then (C1:C3000) a month/year (sept-08) formula showing the date it was opened

 

On a roll up sheet I want to show just the total number of  issues opened up  by product name by month year

 

Product  Sept 09  Oct 09   Nov 09....

A             3            4           6

B             0            2          55

C            40           3           2

 

I may have confused things with the customer name, really I am looking for how many times a month an issue is opened against a product.

 

 

Thanks


NH
NH

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

 
 

Question Info


Last updated August 3, 2020 Views 120,884 Applies to: