•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Countifs with mutiple conditions
Our take
It sounds like you're facing challenges with your COUNTIFS formula, and you're not alone—many users encounter similar hurdles when managing multiple conditions. Your formula attempts to evaluate various criteria, but it seems there's a mistake that's preventing it from triggering the desired results. By refining your approach and ensuring each condition is correctly aligned with the intended outcomes, you can unlock the full potential of your data. Let's work together to simplify this formula and get you back on track.
hello!
if anyone can help will much appreciated been stuck for one week for this.
this my formula.
IFNA(IFS(COUNTIF(G2,"\*DONE\*"),"logged in the system",
SUM(COUNTIFS(K2,{"\*Depreciated\*","\*Depreciated\*"})),"For disposal/salvaged parts",
sum(countifs(c2,{"\*false\*","\*false\*"})),"Get info in the engr. dept"),"For checking")
I know there is mistake in my formula. i just can't correct it yet.
I can't trigger some of the returned conditions.
Edit: should be column in the picture should be results. Thank you for any being kind helping me.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Incomplete formula for ifHello everyone. There is wrong with my formula. Here is the context If the AP.Internal is "billed" equals to recheck the monitoring But If the AP.Internal is "unbilled" equals to recheck the monitoring This condition is same with AP.RAW MATERIALS, AP.Overhead,AP.INVESTOR that if it's billed equals to for managers approval but if not recheck the monitoring But for AP.Supplies, AP.third party, AP.Fees, OT external audit regardless of the status. It should return as "do sanity check" My formula was: If(and(c2="ap.internal",d2="billed"),"for managers approval","recheck the monitoring"),if(and(c2="AP.Supplies, AP.third party, AP.Fees, OT external audit"),"do sanity check","") Before coming up with the formula. I tried to use it works If(and(c2="ap.internal",d2="billed"),"for managers approval","recheck the monitoring") There is something wrong with my formula. I'm not sure if the if formula is most suitable or should be xlookup? Thank you for helping me. But kindly explain to me how can I like understand how do you come up with the correct one. I'm trying to learn be independent working on formulas. submitted by /u/Ok_Vacation_7897 [link] [comments]
- IF/OR Formula with multiple criteria not working as desired (365 for enterprise - beginner)Hey all, I'm trying to make an IF/OR that will generate a "YES" even if only one of the listed criteria is met. Originally I was trying: =IF(OR($B2>6,$C2<80,$D2<3775, $E2="Y"),"NO","YES") Basically, if absences are greater than 6, Grade is lower than 80, STAAR score is less than 3775, OR Discipline = Y, then the output should be NO, the student is not eligible to exempt. However what I'm seeing is that nothing is happening unless I enter the STAAR score. Even at zero absences, the data does not change to YES. SO, I attempted a modified, more specific formula: =IF(OR($B2>6,$C2<80, $C2>0,$D2<3775, $D2>1649, $E2="Y"),"NO","YES") Even if I enter data that should result in a YES in the "Eligible?" column, it remains unchanged until I enter the STAAR score. Other note, I do have conditional formatting on columns B, C, D, and E, but only to change the cell color if certain data are present. For example C2>6 will turn red, C<6, C>2 will turn yellow, and C<3 will turn green. Please advise! Any help is appreciated. submitted by /u/The-Sweetest-Pea [link] [comments]
- Can't find correct place for quotation marks with COUNTIF criteria including mathematical operators and named variableHere’s a section of my table called Visits, which has 5,585 rows: https://preview.redd.it/77rd1pq47mtg1.png?width=398&format=png&auto=webp&s=73e2f78c68ca4477cd380ef60a67ebd79b93bb8a The fiscal year begins on July 1, so the formula in F (Day_of_FYE) is =[@[Visit_Date]]-DATE(VALUE([@FYE])-1,7,0). In another tab I’m trying to figure out how many visits in previous years occurred at the same point in the current year. I have two names defined for the worksheet: CurrentFYE =IF(MONTH(TODAY())<7,YEAR(TODAY()),YEAR(TODAY())+1) DayOfCurrentFYE = (TODAY()-DATE(VALUE(CurrentFYE)-1,7,0)) I know that today is day 280 of the current fiscal. If I use the formula =COUNTIF(Visits[Day_of_FYE],"<=280") I get 3,977, which is correct. But I can’t figure out how to use the mathematical operators with the name of the variable. If I write =COUNTIF(Visits[Day_of_FYE],"<=DayOfCurrentFY") the result is 0. If I write =COUNTIF(Visits[Day_of_FYE],"<=”DayOfCurrentFY) I get the message “There’s a problem with this formula”. Is there no way to use names in criteria like this? Or am I overlooking something else? submitted by /u/fastauntie [link] [comments]
- How to +1 using IF, IFS, SWITCHI am currently trying to do a for a spreadsheet where each time a type of the same incident occurs, 1 is added to the correlating cell. I am struggling to achieve this. I have tried an IFS and SWITCH but neither work, I keep getting the same error. =IFS(A5="Animals",B5+1,A5="Appliances",B5+1,A5="Electricity",B5+1,A5="Fire",B5+1,A5="Heating",B5+1,A5="Lifts",B5+1, A5="Property Damage",B5+1,A5="Water",B5+1, A5="Other",B5+1) =SWITCH(A5,A5="Animals",B5+1,A5="Appliances",B5+1,A5="Electricity",B5+1,A5="Fire",B5+1,A5="Heating",B5+1,A5="Lifts",B5+1,A5="Property Damage",B5+1,A5="Water",B5+1,A5="Other",B5+1) It keeps saying inconsistent error. If I add equal signs in front it completely breaks. If I take the B5 out they are all "1". If I keep it in they are all "0". I just want to make a tally. Can someone help please? Example of what I am trying to achieve Excel is version 2603 build 19822.20182 EDIT 1 - SOLVED but feel free to add feedback. Solution ended up as: =IFS(A13="Animals",COUNTIF($A$4:A13,"Animals"),A13="Appliances",COUNTIF($A$4:A13, "Appliances"),A13="Electricity",COUNTIF($A$4:A13,"Electricity"),A13="Fire",COUNTIF($A$4:A13,"Fire"),A13="Heating",COUNTIF($A$4:A13,"Heating"), A13="Lifts",COUNTIF($A$4:A13,"Lifts"),A13="Property Damage",COUNTIF($A$4:A13,"Property Damage"),A13="Water",COUNTIF($A$4:A13,"Water"),A13="Other",COUNTIF($A$4:A13,"Other")) If you can make it tidier be my guest :) submitted by /u/Advanced-Jelly3774 [link] [comments]