| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 102036 | 2009-08-05 06:32:00 | excel formula | bpt2 (6653) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 798299 | 2009-08-05 06:32:00 | I want to count the cells where two conditions from previous columns are true. What I have tried is: COUNTIF(and(D2:D46,"???2",E2:E46,"M"),h2:h46), but this does not work. Anyone help? | bpt2 (6653) | ||
| 798300 | 2009-08-05 08:42:00 | What are the conditions that have to be true from the previous columns? | beeswax34 (63) | ||
| 798301 | 2009-08-05 08:55:00 | D2:D46 should include text "kmg2" or "kss2" and E2:E46 should = "M" | bpt2 (6653) | ||
| 798302 | 2009-08-05 09:00:00 | If you have Office 2007 you can use COUNTIFS and you won't need the AND statement =COUNTIFS(D13:D17,"???2",E13:E17,"M") |
TeejayR (4271) | ||
| 798303 | 2009-08-05 09:08:00 | I have office 2003 | bpt2 (6653) | ||
| 798304 | 2009-08-05 09:16:00 | If you have a spare column you could concatenate the 2 columns and do your test on that range =COUNTIF(G13:G17,"???2M") |
TeejayR (4271) | ||
| 798305 | 2009-08-05 09:22:00 | How do I concatenate the two columns? | bpt2 (6653) | ||
| 798306 | 2009-08-05 09:25:00 | If you have data in cell b4 and cell c4 you just make the formula in d4 "=B4&C4" or you can use the concatenate formula so D4 would be "=CONCATENATE(B4,C4)" Hope this helps Trev |
TeejayR (4271) | ||
| 798307 | 2009-08-05 09:28:00 | Hi Try this array formula: =SUM(IF(D2 : D46={"kmg2", "kss2"}, IF(E2:E46="M", 1))) After you have entered the formula, do not press Enter but instead press Ctrl+Shift+Enter to convert the formula into an array formula - you will know if you have done this correctly because curly brackets {} will automatically appear around the formula. This works fine with Excel 2003. Andrew |
andrew93 (249) | ||
| 798308 | 2009-08-05 09:35:00 | That's done it, thanks. | bpt2 (6653) | ||
| 1 | |||||