Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CountIF with multiple criteria... couple of problems driving me nuts, help?
I have an interview analysis tool I've built and need the ability to have second level analysis for the different questions. This is basically on the level of "how many people who answered X also answered Y?". Ideally I would be able to do this in three dimensions, but I can probably deal with two if need be. QUESTION 1 - What is wrong with my 2D formula? Found some discussion about doing this with SUMPRODUCT, implemeneted it, and got it to work alright. However, for some reason my validation just won't work correctly. Here's a sample table: Answer 2A Answer 2B Answer 2C Answer 1A Answer 1B Answer 1C And here's the formula: =IF(AND(ISTEXT($A79),ISTEXT(I$97)),SUMPRODUCT(($K$ 8:$K$73=$A79)*($C $8:$C$73=I$97)),"") (Note the numbers seem wierd because the columns being tested aren't actually in the same table like the example... there is one table and then a unified table down the page). Basically it tests for key labels for the two criteria (which in one case is called into the current sheet from a secondary location) and if it finds both (meaning there are criteria for those two columns) runs the routine in that cell. The only thing I can think is that Cell I97 is not "technically" empty... it has a forumla. However, that resolves and would come up as nothing if its blank, right? QUESTION 2 - Can I do this in three dimensions? Right now I'm testing the variables I've already pulled into the sheet. The question is if I can target vairables in the interview sheets themselves (each interview has a sheet, and there's an analysis page for each question)...? Thoughts? Is it possible to successfully do a multi-factor COUNTIF across multiple sheets testing for the two factors in each sheet? Thanks for any help... I'm not much of an Excel whiz and these heavy duty 3D calcs are frying my brain. -rt |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CountIF with multiple criteria... couple of problems driving me nu
On Mar 9, 4:54 pm, Gary Brown
wrote: Chip Pearson's website should help you with your question. http://www.cpearson.com/excel/array.htm Thanks for the reply. I actually figured out my mistake with the first one... guess was right, didn't realize formulas count as values. Changed the test routine and it worked fine. With regard to the link thanks, I'll take a look. I imagine there's got to be a way to do the same thing three-dimensionally, I just need some examples to lead me in the right direction. The routines I write always seem to fail when they try to test the values in the DIRECT call... they make the array fine, but collapse with VALUE when they try to import the values for analysis. -rt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Help, this is driving me nuts | Excel Discussion (Misc queries) | |||
question driving me nuts | New Users to Excel | |||
question driving me nuts | Excel Discussion (Misc queries) | |||
Sum and Count are driving me nuts!! | Charts and Charting in Excel | |||
Driving me nuts. Need more nested than 7 | Excel Discussion (Misc queries) |