sample.xlsxI have a spreadsheet that has three COUNTIFS that asks if cell B2 has "???" and cell C2 has "?????" to add them BUT what I can't seem to do is if A2 has an "x" do not count it.
It also has some double entries, which should not be counted - the names should be counted only once.
I'm using COUNTIFS but I cannot make it so it will not count if an "x" shows up in a certain column.
See attached file.
Last edited by Johfra; 05-22-2014 at 02:20 PM.
your problem you discuss in your post does not seem to match your spreadsheet attached.
For example, I don't see any countif formulas, I don't see anything in cell B2...
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Oh my! So sorry.....I had copied and pasted due to confidentiality reasons (changed info) but the formula didn't follow.
I've reset the formula.
Also, the conditional formatting didn't work (wherever there's an "x" in columns A-F, the row would appear as that color).
Again, sorry about that.
Ok, still needing more clarification. You write that cell B2 contains "???" and C2 has "?????" and I don't see those cells outside of that they are part of the merged column rows 1-14 and I don't see the questionmarks either. Do they factor in to the equation?
Plus, what you note as your calculation and what the correct answer should be, your calculation looks to be getting the right number so I'm not sure what other factor you want added to get the "correct answer".
Finally, you mention that wherever there is an X in columns A-F you want the row to appear as that color. So you want to transpose the pink color in col E to be pink across rows 17, 18 and 19 and the blue that is in col D to be across rows 16, 21 and 23 for example?
I'm sorry for the confusion. I'm really horrible at explaining myself.
OK - lemme try this again (thank you for your patience):
Please omit my first examples (B2 has ???, etc.) - when I first wrote it, I didn't have any intention of attaching a sample. I then thought it over and attached it with some modifications (I was so focused on removing private information I forgot to correct the post).
Whenver there is an "x" in column D, the countifs should NOT count it. That's why we should refer to "CORRECT ANSWER". Basically, the docs want to know how many times the patient was transferred from one destination site to another (RVH to MGH). If there was no transfer (through conditional formatting, the row will become blue), it is irrelevant. - MY CALCULATION through COUNTIFS do not factor the ones that should not be counted....I have tried many variations and have come out with all the wrong answers.
The existing "totals" in D29 & G29 are for other statistal reasons and I have already incorporated them - they are not necessary for this question.
***crossing fingers this makes more sense now*** LOL
Ok, still another question. You mention that it shouldn't count if there is an "x" in col D, what about other columns that have an "x"? So far I just added this to your formula and got your "correct answers" down through MGH-MCI, but not in MGH-MNH and I think that is because you have the "x" in col E.
=COUNTIFS($J$14:$J$27,"RVH *", $K$14:$K$27,"MGH *",$D$14:$D$27,"")
EDIT: moreso, does it matter if there is an "x" in any of columns A-F?
Last edited by Sam Capricci; 05-21-2014 at 03:15 PM.
heading out for the day so, I'll just give you how I'd do it. I'd use a helper column, say col L. I'd put this in it =IF(D14="x","x",IF(E14="x","x",IF(F14="x","x","")))&LEFT(J14,3)&"-"&LEFT(K14,3)
or this
=IF(A14="x","x",IF(B14="x","x",IF(C14="x","x",IF(D14="x","x",IF(E14="x","x",IF(F14="x","x",""))))))&LEFT(J14,3)&"-"&LEFT(K14,3) if you want to include all the columns from A-F.
Then use this for the countif... =COUNTIF($L$14:$L$25,""&G31)
I get answers that are close to yours (some match, some don't) but where I left things I wasn't completely sure which of the columns A-F that had x's you were counting.
Good luck.
THANK YOU! THANK YOU! THANK YOU!
Yay....I'm relieved I was able to get my point across!
The codes works perfectly! I did some minor adjustments cause I don't need to count the "x" in the other columns but the one in column D.
I appreciate so very your help!
THanks again!
Great! Glad you could tweak it to work how you needed.
AND, thank you for the bump to my reputation!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks