PDA

View Full Version : excel formula



bpt2
05-08-2009, 06:32 PM
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?

beeswax34
05-08-2009, 08:42 PM
What are the conditions that have to be true from the previous columns?

bpt2
05-08-2009, 08:55 PM
D2:D46 should include text "kmg2" or "kss2" and E2:E46 should = "M"

TeejayR
05-08-2009, 09:00 PM
If you have Office 2007 you can use COUNTIFS and you won't need the AND statement

=COUNTIFS(D13:D17,"???2",E13:E17,"M")

bpt2
05-08-2009, 09:08 PM
I have office 2003

TeejayR
05-08-2009, 09:16 PM
If you have a spare column you could concatenate the 2 columns and do your test on that range

=COUNTIF(G13:G17,"???2M")

bpt2
05-08-2009, 09:22 PM
How do I concatenate the two columns?

TeejayR
05-08-2009, 09:25 PM
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

andrew93
05-08-2009, 09:28 PM
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

bpt2
05-08-2009, 09:35 PM
That's done it, thanks.