help with drawing data from filtered results

[mungojeerie]

I have a formula that tells me the number of instances a record appears based on several variables.

the data that this formula draws from is in several columns and each columns header is autofiltered.

Header examples are as follows A- Date, B- Client, C- Model, E- Color etc.

The data can be easily filtered in place to show all instances of a particular date or model number or Color etc.

The table that contains the formula tells me for example how many instances model xyz occours in the color red. This formula draws data from all entries.

I would like to change it to draw data from the filtered results so that I can filter the data by date or customer etc and therefore specify that the formula show me the number instances model xyz occours in red within the filtered results.

The formula Im using to gather the data is as follows:
=SUMPRODUCT(--(ISNUMBER(SEARCH("15.5",$C$44:$C$542))),--($E$44:$E$542="Red"),--($D$44:$D$542="Sport"))

Thank you for looking in advance.

Ryan

 

[GlennUK]

Look at this:

http://www.contextures.com/xlfunctions04.html#Visible

 

[mungojeerie]

Thank you glenn, I also use this formula in that worksheet for something else: =SUBTOTAL(3,$C$52:C1000) which is the same that you posted for me to look at.

In fact this is the reason I thought, hey... I know it can be done, but how...

how do I work that formula in with my current formula. I basically want to combine the following:

=SUBTOTAL(3,$A$52:A1000) with =SUMPRODUCT(--(ISNUMBER(SEARCH("15.5",$C$44:$C$542))),--($E$44:$E$542="Red"),--($D$44:$D$542="Sport"))

 

[GlennUK]

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A44:A542,ROW(A44:A542)-MIN(ROW(A44:A542)),,1)),--(ISNUMBER(SEARCH("15.5",$C$44:$C$542))),--($E$44:$E$542="Red"),--($D$44:$D$542="Sport"))

as a guess. You'll have to test it. I'm about to log off, and can't test it myself.

 

[mungojeerie]

Glenn! Excellent my good man! it works wonderfully. thank you kindly!