• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help please! How can I exclude parameters from a formula?

S

Samantha S

New Member
I've created a document to track the proficiencies of employees based on job function.

I have a formula setup to exclude users from the proficiency impact for 120 days (row 4, column M), while they are learning the job. I've now been asked to exclude the "Legacy" system (column B) for new hires, so in theory, the employee in column I should be excluded.

We've tried several formulas, but are striking out. Is there a way, to have the lower table countif 0, 1, or 2 but exclude cell if system = legacy?

Or would it be as simple as the table array only including the new system? Since that is how it's sorted.
 

Attachments

  • TLP_2016.xlsx
    27.4 KB · Views: 10
Hi ,

I am not clear on where your formula needs to be entered , and what exactly the formula should do ; can you explain with reference to Excel entities , rather than use terminology which may not be familiar to others.

The worksheet tab named Standard Maintenance (HV) has the following formula in L5 :

=SUMIF($E$4:$J$4,">120",E5:J5)

The worksheet tab named Standard Maintenance (LV) has the following formula in M5 :

=SUMIF($E$4:$K$4,">120",E5:K5)

Which of these needs to be modified , and how ? Can you explain with reference to worksheet cells ?

Narayan
 
Whoops. Sorry Narayan. I always leave something out.

I'm not too good with Excel entities but will try my best.

I need for the formula in cells I28-I32, to exclude any where in column B:B on the HV/LV tabs where the system says "Legacy"

Is it possible for something like "=Countif(I5:I24,0) "except if B:B='legacy'"?

I'm not sure if I should use IF Statements, the Bolded underline is the part that is tripping me up.
 
Hi ,

Would it be the following ?

=COUNTIFS(I$5:I$24,0, $B$5:$B$24,"<>Legacy")

=COUNTIFS(I$5:I$24,1, $B$5:$B$24,"<>Legacy")

=COUNTIFS(I$5:I$24,2, $B$5:$B$24,"<>Legacy")

Narayan
 
You are a genius! Yes, this works!! Thank you thank you thank you.

I've been wracking my brain trying to figure this out.

Very thankful for you Excel Ninjas!
 
You must log in or register to reply here.
Back
Top