My current formula (that works) is: =COUNTIFS(O3,">3",N3,">3",M3,">3",L3,">3")
I need to indicate that N3, M3 & L3 can be either ">3" OR "D"
I've tried to insert OR statements prior to each comma (to no avail). It's probably as simple as a () issue.
I'm sure for anyone who frequents an Excel Forum, this is very elementary; however I'm learning as I go on this one. Help with this formula would be greatly appreciated.
Thanks! C---
if that works hen so should
COUNTIFS(O3,">3",N3,">3",M3,">3",L3,">3")+COUNTIFS(O3,"D",N3,"D",M3,"D",L3,"D")
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
From your post, hope this will help you:
Please Login or Register to view this content.
I tried this one, but it still didn't give me the expected response. I could have any combination of "D" or "#>3". So, I would like the formula to count if the L3:O3 cell sequence is 4 D 5 5, or D D D D, or 4 4 4 5, or D 5 4 D.... Any combination of >3 or D should be counted.
I think the way your formula reads, it only accepts a group of numbers >3 or a group of D's.
I hope this makes sense. I don't speak Excel very well...
COUNTIF/S and SUMIF/S are very picky about the criteria fields, they don't allow much in the way of manipulating data there,
you can use this Array Formula (Cntrl+Shift+Enter, not just enter):
Formula:
Please Login or Register to view this content.
Hope this helps
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
Maybe..
Please Login or Register to view this content.
Life's a spreadsheet, Excel!
Say thanks, Click *
YAY! Thank you SDCh! It worked! And Holy Moly! No wonder I couldn't figure it out! haha!
COUNTIF/S and SUMIF/S are very picky about the criteria fields, they don't allow much in the way of manipulating data there,
you can use this Array Formula (Cntrl+Shift+Enter, not just enter):
Formula:Please Login or Register to view this content.
Hope this helps
Edit-
thats why the array formula, if any of them, including O3I need to indicate that N3, M3 & L3 can be either ">3" OR "D"
then this regular formula should work
Formula:Please Login or Register to view this content.
Edit 2-
corrected the ranges in 2nd formula
Edit 3-
max not needed..sigh so this will work:
Formula:Please Login or Register to view this content.
the MAX was leftover from earlier attempt, it didn't hurt the outcome, but was unneccessary
Last edited by dredwolf; 02-09-2013 at 03:51 PM.
I think (so far) the =SUMPRODUCT that SDCh provided seems to be doing the trick.... although I have no idea how. lol
If I run into a snag during the application of the formula in other areas of the sheet, I will definitely try the other options here. What I think I've learned is there seems to be "more than one way to skin a cat" with Excel.
Thanks so much to all of you for coming to the aid of a novice.
Re: Help with COUNTIFS formula containing ORs STILL UNSOLVED FOR THE MOMENT
Well..... I THOUGHT it was solved. Sorry, guys.
I've tried all the formulas in this thread, and none work. SO, if any of you (or others) want a challenge, I am attaching a file that shows a screenshot and gives a little more background and detail.
Both screenshots are (to me) unreadable...please upload a sample workbook, (edited for privacy), showing the format of data you have, expected output and WHY that is the expected output..
=SUMPRODUCT(--ISNUMBER(L3:O3)*(L3:O3>3))+COUNTIF(L3:O3,"d")
Re: Help with COUNTIFS formula containing ORs
Let me know if this helps you help me.
This one is offering a value of 4. The only expected values are 0 or 1.Originally Posted by martindwilson
Column O is the one in question, but it's COUNTIF value is contingent upon the other columns. In order for Column O to be counted, it must be 4,5 or D AND the other Columns (LMN) must ALSO be 4,5,D.
So, just to be strait here, column O ONLY gets added if the L,M,N columns ALL match the >3 or ="d" ?
correct. O is the target, but it is contingent on LMN having the same score range 4, 5 or D.
The words get harder from Word Reading 1 to Word Reading 4. I highlighted (and want to count) the first place of failure, and now I want to tally the student's level of success in each of the sections (Red=Severely at risk, Orange=below level, Yellow=Borderline, Green=Met Standard, Blue=Exceeded Standard).
I have analyzed the parts. Now I want to summarize the information in order to evaluate student's reading ability as a whole.
Were you able to open the workbook sample? I can talk you through that if you are looking at it. There is an explanation on the 2nd sheet
Last edited by ConnieIrene; 02-09-2013 at 09:38 PM.
But you want a 0 or 1 result from this formula (true or false basically) ?
yes. 0 or 1 (count column O or don't count it). The other "hide" columns were easy (for example column AK) because in the Phonemic Awareness section (which has 4 Tasks to administer) & the Grapho-Phonemic Knowledge section (which has 5 tasks to administer), the test STOPS at the first failed task; however with WORD READING, you administer ALL 4 SETS regardless of success or failure. I'm analyzing the FIRST incident of failure, no matter how they did on the other 3.... So thus my issue with the contingent columns.
It might be easier to work on column M or N first actually... N is counted if N>4 but L and M are 4, 5, or D. HAHA! It's crazy, isn't it???
Bless your heart... you deserve a gold crown! Dredwolf, thank you for sticking with me on this. I know it's a hot mess and you are trying to decipher the ramblings of someone who doesn't know excel all that well....
try again
=--AND(SUMPRODUCT(--ISNUMBER(L3:N3)*(L3:N3>3))+COUNTIF(L3:N3,"d")=3,OR(O3={4,5,"d"}))
OOOO OOO OOO! I think that might be it! I'm going to revise it for the other columns and test it for other student samples before I consider it solved for sure this time. Guys, either way, you are AWESOME, and I appreciate you for hanging with me SOOOO much!
It seems to work! In revising for the other Word Reading sections, I was able to Count Green responses with this edited version of your formula
=--AND(SUMPRODUCT(--ISNUMBER(L3:N3)*(L3:N3>3))+COUNTIF(L3:N3,"d")=3,OR(O3<4))
Blue (the one you did) means ALL 4 sections are Developed. Green (the one above) means the first 3 sections (LMN) are developed but the 4th (O) is not.
So, it would stand to reason that the others would work upon editing as well, but I can't elicit the expected response for Yellow or Orange. They both yield all 0's when I test them. Below is what I tried....
For Yellow (ColumnAM), L & M are developed, but N is failed (<4) and O is not considered
=--AND(SUMPRODUCT(--ISNUMBER(L3:M3)*(L3:M3>3))+COUNTIF(L3:M3,"d")=3,OR(N3<4))
For Orange (ColumnAF), L is developed, but M is failed (<4) and N & O are not considered
=--AND(SUMPRODUCT(--ISNUMBER(L3)*(L3>3))+COUNTIF(L3,"d")=3,OR(M3<4))
Last edited by ConnieIrene; 02-09-2013 at 10:28 PM.
Never Mind.
=--AND(SUMPRODUCT(--ISNUMBER(L3:M3)*(L3:M3>3))+COUNTIF(L3:M3,"d")=2,OR(N3<4))
=--AND(SUMPRODUCT(--ISNUMBER(L3)*(L3>3))+COUNTIF(L3,"d")=1,OR(M3<4))
THANK YOU, THANK YOU, THANK YOU! Now that I have the formulas down, I only have......twenty.....three.... more sheets to go....haha!
You
are
amazing!!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks