Need to count if X = 1.2 and Y = green... help

[caitfox]

Hello
I wonder if somebody could correct my formula here as I'm not quite sure where I have gone wrong.
I have a set of data as below:
Number / Status
1.2 / Red
1.2 / Green
1.2 / Amber
1.3 / Green

In another sheet I want to pull back the number of times where 1.2 AND Green occur. So in the above example the answer should be 1.

I have:
=(((COUNTIF('sheet1'!A:A,"1.2")*AND(COUNTIF('sheet1'!J:J,"Green")))))
where sheet1 is my sheet name and A:A is the column that 1.2/1.3 appear in and J:J the column where the colour appears, but this is not showing the correct answer...

Thanks

[ChemistB]

Several issues
1.2 should not be in quotes.
You want to use COUNTIFS (which is available in 2007) not the AND
=COUNTIFS('sheet1'!A:A,1.2,'sheet1'!J:J,"Green")

[NBVC]

Since you have Excel 2007, Try:

=COUNTIFS('sheet1'!A:A,"1.2",'sheet1'!J:J,"Green")

[NBVC]

Since you have Excel 2007, Try:

=COUNTIFS('sheet1'!A:A,"1.2",'sheet1'!J:J,"Green")

[caitfox]

How about if I wanted to pull through

When 1.2 appears in column A, and Red, Green or Amber appear in column J - and I want to formula to bring back the colour that appears most frequently in column J (but only where column A=1.2).

Thanks... sorry, I've been staring at spreadsheets for too long today and I've lost the plot!

[NBVC]

Try something like:

=INDEX($J$1:$J$100,MODE(IF($A$1:$A$100="1.2",MATCH($J$1:$J$100,$J$1:$J$100,0))))

adjust ranges to suit. (don't use whole columns) and then confirm the formula with CTRL+SHIFT+ENTER not just ENTER.

Note: If the 1.2 is in fact a number entry and not a text, then remove the quotes.