Formula to count after filtering data

redspanna

Well-known Member
I have a sheet that contains several thousand rows of data that I will filter to get what I need.

Basically I would like a formula or maybe code that will count the number of times the number '1' is shown through the filtered rows in column K IF the date value through the filtered data in column A is equal to a date that is shown in cell A1 of sheet2.

So If date in A1 sheet2 is 30Aug14, count how many times the number 1 is shown in the same filtered data through column K

Hope this makes sense and thanks in advance

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Try this:

=SUMIF(A:A,Sheet2!\$A\$1,K:K)

In assuming that your data (cols A and K ) is in the sheet where the formula is going.

Hi Hercules1946

Regret the result I get is Zero (0) even though if I do a quick =SUM(k:k) FORMULA I get the correct result needed.
I have made sure that the date through column A is the same as that in cell A1 / Sheet2 so not sure why the formula doesn't work

That suggests that you might be referencing the wrong column somewhere in your formula.
The formula doesn't need the filtering, it will scan all the data without it. See if it works when you don't use the filter.
To replicate my example:

1 In the current sheet Col A, copy the date 30-Aug-14 into A1 to A23
2. Change (say) 10 0f these to another date.
3 In Col K, enter 1 in cells K1 to K23.
4 In Cell K25 enter the formula: =SUMIF(A:A,Sheet2!\$A\$1,K:K )
5 On Sheet2, enter the date 30-Aug-14 into cell A1
6 Return to the sheet with the formula and K25 should show a value of 13.

HTH

Perhaps this will help. It works with and without filtering.

Excel 2010
ABCDEFGHIJK
108-Aug-14
230-Aug-141
308-Aug-14
408-Aug-14
530-Aug-141
608-Aug-14
708-Aug-14
808-Aug-14
908-Aug-14
1030-Aug-141
1108-Aug-14
1208-Aug-14
1330-Aug-141
1408-Aug-14
1508-Aug-141
1630-Aug-141
1730-Aug-141
1808-Aug-14
1908-Aug-14
2008-Aug-14
2130-Aug-141
2230-Aug-141
2330-Aug-141
24
259
Sheet1
Cell Formulas
RangeFormula
K25=SUMIF(A:A, Sheet2!\$A\$1,K:K )

I have a sheet that contains several thousand rows of data that I will filter to get what I need.

Basically I would like a formula or maybe code that will count the number of times the number '1' is shown through the filtered rows in column K IF the date value through the filtered data in column A is equal to a date that is shown in cell A1 of sheet2.

So If date in A1 sheet2 is 30Aug14, count how many times the number 1 is shown in the same filtered data through column K

Hope this makes sense and thanks in advance

Try...
Rich (BB code):
``````=SUMPRODUCT(SUBTOTAL(2,OFFSET(K2,ROW(K2:K100)-ROW(K2),0,1)),
--(K2:K100=1),--(A2:A100=Sheet2!A1))
``````

Hi Hercules1946
=SUMIF(A:A, Sheet2!\$A\$1,K:K ) works great but can I ask if it can be tweaked slightly.
I also only want column K counted if the date is matched AND column C has 'dep' in any cell

so I guess thats a SUMIF AND formula ?

thanks

I have a sheet that contains several thousand rows of data that I will filter to get what I need.

Basically I would like a formula or maybe code that will count the number of times the number '1' is shown through the filtered rows in column K IF the date value through the filtered data in column A is equal to a date that is shown in cell A1 of sheet2.

So If date in A1 sheet2 is 30Aug14, count how many times the number 1 is shown in the same filtered data through column K
You might want to check out the SUBTOTAL function in Excel's help files. To do that, select a blank cell, click the "fx" button next to the Formula Bar, type SUBTOTAL in the highlighted "Search for a function" field, then click the "Go" button and then click the "Help on this function" link at the bottom of the dialog box.

Hi Hercules1946
=SUMIF(A:A, Sheet2!\$A\$1,K:K ) works great but can I ask if it can be tweaked slightly.
I also only want column K counted if the date is matched AND column C has 'dep' in any cell

so I guess thats a SUMIF AND formula ?

thanks

So, you are not applying autofilter to your data at all?

Sorry, but Im not clear on this. Im unsure because you use the words "I also".
Are you saying that its counted if the date in A:A matches Sheet2!A1 AND "dep" is in C:C ?
The other altenative is : its counted if the date in A:A matches Sheet2!A1 OR "dep" is in C:C

Assuming the first we can use SUMIFS if you have Office 2007 or later.

Which one?

Last edited:

Replies
13
Views
726
Replies
2
Views
337
Replies
3
Views
337
Replies
4
Views
350
Replies
0
Views
457

1,221,119
Messages
6,158,051
Members
451,459
Latest member
Austin2550

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.

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