Forumla help: Cells in A and B are filled AND have "3" in row C

[Bear1985]

Hi everyone,

I am having quite a bit of trouble with a table my boss asked me to do. I have a large excelsheet filled with information from my colleagues.

I want to find out how many meetings there are this week where people from Department A AND B are present.

In Column A are the colleagues from Department A who will participate in the meeting. If no one is participating, the cell is left empty.
In Column B are the colleagues from Department B who will participate. If no one is participating, it's also left empty.

In Column C is the Kalender-Week (eg. 3)

Basically I need a formula that will tell me how many meetings there are THIS WEEK in which BOTH departments are present.

I have a german version of excel, but an english formula would be fine - I can "translate"

Thanks in advance!

I have tried Sumproduct, Counta and countif, but I can't do it

[Bob Phillips]

Try

=SUMPRODUCT(--(A2:A20<>""),--(B2:B20<>""),--(C2:C20>=--"2012-01-16"),--(C2:C20<"2012-01-16"+7))

you can replace the embedded dates with a cell reference.