ChristianR

iTrader: (1)

hi, need your help on a forumla for excel.

I have column H which has either Bike or Car in it.
column J has either Yes or No in it.

I want a formula that will count all fields which has Bike and Yes and give me a total, and a sep one for cars.

So I can clearly see how many cars or bikes are entered.

Thanks.

DemonDave

iTrader: (13)

add a column K with the formula =H1&"-"&J1 for each row

then at the bottom of that column = COUNTIF(K1:K2,"bike-yes") repeat for "car-yes"

that will do it

DJ Dunk

iTrader: (5)

I'd do it slightly differently . . .

=SUM(IF(H2:H65536="Bike",IF(J2:J65536="Yes",1,0),0 ))

You'd enter that and press Ctrl+Shift+Enter to tell Excel it must treat this as an array formula.

chriswsti05

iTrader: (1)

or use a pivot table !

ChristianR

iTrader: (1)

cheers guys, dj dunk's worked perfectly

ChristianR

iTrader: (1)

One more thing... is this possible?

As mentioned above, column H has either Bike or Car in it, column J has either Yes or No in it. As well as this I have column B & C. B has a number in it, C has a name.

I would like a query that will check to see if column H = Car, and column J = Yes. If that is matched, it will the pull all the people who has this info and display columns b & c in two new fields in the spreadsheet, and if possible display them in column b order.

I know lot to ask!

thanks