how do i create a SUMIF to ignore cells hidden with autofilter

I have a formula to search a sheet for certain criteria and sum up the total, data is entered into this sheet by week number, where I then filter it for whichever week I need. I following formula works fine, but adds all the cells including hidden ones, how can i get it to ignore hidden cells??

=SUM(IF('FT Line 1-RX'!$C$2:$C$2100='FT costs 1-Rx'!B$1,IF('FT Line 1-RX'!$E$1:$GT$1='FT costs 1-Rx'!$A11,'FT Line 1-RX'!$E$2:$GT$2100)))

There is raw data in the 'FT Line 1-RX' sheet which is summed up in the 'FT costs 1-RX' sheet.

Does this work? ... not tested...

If it doesn't can you post your workbook - minus confidential stuff...

Please Login or Register  to view this content.

Hi,

While doing autofilter you would have applied some condition..

Why dont u add tht condition to sumproduct to get the sum for only visible row.

Example... Suppose you autofiler Column A for values "Apple", this mean other values not all hidden

=Sumproudct ( your condtion 1 , your condition 2 , A:A ="Apple")

Originally Posted by Domenic

Try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet1!$C$2:$C$14,ROW(Sheet1!$C$2:$C$14)-ROW(Sheet1!$C$2),0,1)),--(Sheet1!$C$2:$C$14=Sheet2!B$1),INDEX(Sheet1!$E$2:$H$14,0,MATCH(Sheet2!$A2,Sheet1!$E$1:$H$1,0)))

Hope this helps!

Very nice, Domenic

Originally Posted by JONNY981

This worked perfectly,thanks! .....But, as that was a small sample sheet, i now have to incorporate this into a much larger sheet. The columns (i.e the fail reasons) on this sheet go to up to GV, and the rows (i.e the weeks) will probly end up going up to around 4000 (new data is entered on a daily basis), I'm just trying to change the formula in order for it to work on the main sheet, what exactly do i need to change?

Sorry, havent used SUMPRODUCT or SUBTOTAL before(i'm a bit useless after basic formula!)

Thanks

Assuming your column titles in A:H don't change and you just have more titles...

then change all the 14's in the formula to 4000 and all the H's to GV.

You will probably need to change the sheetname, Sheet1! to 'FT Line 1-RX'! also, per your original...

and you probably can delete all the references to Sheet2! since the formula is on the active sheet...