CountIF - combine results from two columns

Hi,

I have two columns A and B. Column A contains 'Machine Nr' and column B contain 'Week Nr'. In cell C1 i want to write a function so that it counts the number of weeks in Column B where week number is 4 AND the machine nr is 1 in column nr A. Can anyone help me here plzzzz.

Column A Column B
Machine Nr Week Nr
1 4
1 4
2 4
2 4
3 4
3 4

Perhaps:

=SUMPRODUCT(--($A$1:$A$100=$C$1),--($B$1:$B$100=$D$1))

where C1 is cell containing Machine No of interest (1) & D1 is cell containing week no of interest (4)

If you're using XL2007 you could use COUNTIFS as opposed to SUMPRODUCT.

Please have the decency to test the solution provided before dismissing.

I'm not quite sure what you're trying to achieve but based on your sample you should use:

C2: =SUMPRODUCT(--($A$2:$A$2000=$A2),--($B$2:$B$2000=$B2))
copy down

Without knowing as to the purpose of the sheet in question it's hard to comment further but given volume of calcs being performed there is invariably a better approach but we would need to understand how these results are to be used.

EDIT:

For ex C could create a concatenation

C2: =$A2&"~"&B2
copied down

Then you can avoid array and use COUNTIF

D2: =COUNTIF($C$2:$C$2000,$A2&"~"&$B2)
copied down

More efficient than Sumproduct.

You didn't adjust the formula in any way for your own data?

In C2:
=SUMPRODUCT(--($A$1:$A$3100=A2),--($B$1:$B$3100=4))

You have to expand that range to cover your data, and make the EQUAL comparisons match. You were still using C1 and D1 and there was nothing in those cells to match.

efilipe, please do not hijack another's thread, you should create your own.