Formula to count after filtering data

[redspanna]

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

 

[Hercules1946]

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.

 

[redspanna]

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

 

[Hercules1946]

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

 

[Hercules1946]

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

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	08-Aug-14
2	30-Aug-14										1
3	08-Aug-14
4	08-Aug-14
5	30-Aug-14										1
6	08-Aug-14
7	08-Aug-14
8	08-Aug-14
9	08-Aug-14
10	30-Aug-14										1
11	08-Aug-14
12	08-Aug-14
13	30-Aug-14										1
14	08-Aug-14
15	08-Aug-14										1
16	30-Aug-14										1
17	30-Aug-14										1
18	08-Aug-14
19	08-Aug-14
20	08-Aug-14
21	30-Aug-14										1
22	30-Aug-14										1
23	30-Aug-14										1
24
25											9
Sheet1

Cell Formulas
Range	Formula
K25	=SUMIF(A:A, Sheet2!$A$1,K:K )

 

[Aladin Akyurek]

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

=SUMPRODUCT(SUBTOTAL(2,OFFSET(K2,ROW(K2:K100)-ROW(K2),0,1)),
  --(K2:K100=1),--(A2:A100=Sheet2!A1))

Adjust the ranges to suit.

 

[redspanna]

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

 

[Rick Rothstein]

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.

 

[Aladin Akyurek]

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?

 

[Hercules1946]

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?