Maksym 0 Posted October 16, 2008 Dear Atomic, I'm desperate to get some advice to an excel formula problem. I need a simple formula for counting the amount of times two criteria are said in a given range. Countif - has worked for a single criteria, eg. =COUNTIF('Sheet 1'!F:F,"T2") - Counting the words T2 in column F of sheet 1. I want to count Critera A in column F and Criteria B in column G, however, =COUNTIF('Sheet 1'!F:G,"T2"&"Yes") does not work! How do I do it? For example: A B C D 1 T1 Yes X 2 T2 No Y 3 T1 No Z 4 T2 Yes Y T2 & Yes = 1 The only way I've found of doing it is to =CONCATENATE(A1,"-",B1) to give you T1-Yes, and so on. Is there an easier way? Any help would be greatly appreciated! Share this post Link to post Share on other sites

dmj 0 Posted October 16, 2008 If statements can be nested in excel. Give them a go. Share this post Link to post Share on other sites

robzy 166 Posted October 16, 2008 I want to count Critera A in column F and Criteria B in column GAnd do what? Add them together? Multiply them? Subtract them? If add them... =COUNTIF('Sheet 1'!F:F,"T2") + COUNTIF('Sheet 1'!G:G,"Yes") That should work, shouldn't it? Rob. Share this post Link to post Share on other sites

Chaos.Lady 331 Posted October 16, 2008 No Robz. =If (COUNTIF('Sheet 1'!F:F,"T2"), COUNTIF('Sheet 1'!F:G,"T2"&"Yes")) or something like that. My excel's rusty. Share this post Link to post Share on other sites

1shot1kill 36 Posted October 16, 2008 And do what? Add them together? Multiply them? Subtract them? If add them... I think he just wants to know how many times the criteria occurs. I could be wrong here. Share this post Link to post Share on other sites

robzy 166 Posted October 16, 2008 And do what? Add them together? Multiply them? Subtract them? If add them... I think he just wants to know how many times the criteria occurs. I could be wrong here. Ohh... so he want's to know how many rows have both conditions true? Actually, reading the bit about the concatenate statement that makes sense. I'd just use the concatenate statement I think Rob. Share this post Link to post Share on other sites

NightOwl 91 Posted October 16, 2008 countif will only work with one criteria. sumproduct will do what you want. assuming you want to count the number of times t2 and yes appear together in the same row... =SUMPRODUCT((B1:B4="t2")*(C1:C4="yes")) or for your example ... =SUMPRODUCT((Sheet 1!F1:F1000="T2")*(Sheet 1!G1:G1000="Yes")) another way of doing it is with a sum array... =SUM(IF(B1:B4="t2",IF(C1:C4="yes",1,0),0)) as this one's an array formula, you must ctrl+shift+enter for it to commit. hope that helps :) Share this post Link to post Share on other sites

psyckle 0 Posted October 16, 2008 An easy way I do it when I can't remember the countif statement syntax if add an additional column, make the formula (assuming T1 column is column B) =if(C1="T2",1,0) then sum column C for your total. sometimes I find it easier than remembering syntax. then for second criteria put a second column, take sum etc. Share this post Link to post Share on other sites

Maksym 0 Posted October 16, 2008 I just got into work so i'll give the suggestions a go... Thank you so much, i hope they work... Edit: Yes, i did want to know how many rows have both conditions true. Share this post Link to post Share on other sites

robzy 166 Posted October 17, 2008 NightOwl: You do realise that I will now be directing all my Excel questions to you, right? :P Rob. Share this post Link to post Share on other sites

DJRmenace 0 Posted October 24, 2008 OK, if you have ever used Array formulas (The ones with the "{" and "}" brackets that you need to use "Shift + Enter" to enter in) I suggest using an Array formula. It is technically the most efficient way to get the result you want by only entering a singe formula in one cell. Your other suggestions (Concatenate etc) theoretically should not work whithout having to type it in a cell on each row which works but is not the most efficient. eg. "x" represents your data, "f" represents where your concatenate formula would go, "c" represents where your countif formula would go to count through the results of the concatenations above. A B C D 1 x x x f 2 x x x f 3 x x x f 4 x x x f 5 c This is not the smartest way but will work. BEST SOLUTION: On the other hand an array formula would get the first set of counf if results and store them in an invisible array, then excecute the next countif on the remaining results and then provide your answer in the cell. I do not recommend using this option if you have never delved in to array formulas before if you need a quick fix as it will take you longer to learn and understand the syntax for array formulas thanit would to just use the concatenate work-around. BUT if you are keen to be an Excel pro check it out. I am about to give you a solution that works but I won't explain it too much, if you are a logical thinker you'll be fine Eg scenario similar to yours: The data: ..___A___B___C___D___E 1___1___A 2___1___A 3___2___A 4___2___B 5___2___B 6___1___B 7___1___A 8___1___A 9___2___A 10__1___B 11__2___B 12__1___B 13__1___A 14 15 Lets say you want to know how many times Colum A is "1" and Colum B is "A" on the same line. You would then go to a spare cell (say C14 for example) Then enter the array formula below by pressing "Shift + Enter": =SUM(($A$1:$A$13=1)*($B$1:$B$13="A")*1)Enter that code in the formula bar and instead of pressing enter to move on you must press "Shift + Enter"Note: IF YOU DO NOT PRESS SHIFT + ENTER TO ENTER THIS FORMULA IT WILL NOT SAVE AS AN ARRAY FORMULA. AS A NORMAL FORMULA THIS FORMULA IS USELESS Try it as is on the above data first to get your head around it. (set up the above scenario and exact array formula as is in excel). The result will show as "5" if you have done it right as there are 5 lines matching the criteria "Colum A is "1" and Colum B is "A" on the same line". Now you are a real excel pro! :p I bet you never new they had this. Check out Microsoft Excel's help info on Array Formulas for more cool stuff it can do :) If this works, and you happen to know a php expert, see if they might be able to help me with my "PHP Quick question" post at: http://forums.atomicmpc.com.au/index.php?showtopic=3954 Looks more complicated but isn't really. By the way...... ELVENWHORE FTW!!! DJ. Share this post Link to post Share on other sites