IF Statement Help

[forbudt4u]

Hello, I'm having an issue with a couple IF statements, if that's even what I need to use... I'm hoping someone can help. On the first one, in Cell L4006 I am attempting to have a formula search a range (Status, which is J2:J4004) and give me a count of instances in the range where the text "Missing" is present and give the sum of all of those instances. However, I want it to work after filters are applied counting just the results contained in the fultered results... much like the SUBTOTAL function.

The other formula I will need help with is in Cell M4006 where I will be doing something similar but needing the formula to search a separate range (Cost, which is G2:G4004) and summing up just the costs associated with any cells in the status range containing "Missing". Oh, and just like before, I am needing to adjust results if filters are applied.

I'm hoping I don't need code for this... any help would be greatly appreciated.

 

[Eric W]

How about:

=SUMPRODUCT(SUBTOTAL(103,OFFSET(J2,ROW(J2:J4004)-ROW(J2),0)),--(J2:J4004="missing"))

and

=SUMPRODUCT(G2:G4004,SUBTOTAL(103,OFFSET(J2,ROW(J2:J4004)-ROW(J2),0)),--(J2:J4004="missing"))

 

[mole999]

if you could shift the cells down one from the top, you could maybe use AGRRAGATE in the top line and as you apply filters it can ignore hidden etc

 

[forbudt4u]

How about:

=SUMPRODUCT(SUBTOTAL(103,OFFSET(J2,ROW(J2:J4004)-ROW(J2),0)),--(J2:J4004="missing"))

and

=SUMPRODUCT(G2:G4004,SUBTOTAL(103,OFFSET(J2,ROW(J2:J4004)-ROW(J2),0)),--(J2:J4004="missing"))

This worked flawlessly! Really appreciate it, Eric!

 

[Eric W]

Glad we could help! Thanks for the feedback! ?