I'm trying to do a count if with 2 criteria - the difficulty I'm having is
the 2nd criteria involves a calculation - please see example below;

Table 1
Col A Col B
ABCD 20/10/2005

Table 2
Col A Col B
ABCD 20/05/2005
ABCD 20/09/2005

I want to count if Col A from Table 2 matches A1 from Table 1 plus B1 from
Table 1 less Col B in Table 2 is greater than 30. I'm probably not explaining
this very well but the answer would be 1. My problem is I can't figure out
how to get avalue for B1 (Table 1) against every row in Col B in table 2. Am
I making any sense at all?

=SUMPRODUCT((Sheet2!A1:A100=Sheet1!A1)*(Sheet1!B1-Sheet2!B1:B10030))

HTH
Kostis Vezerides

On Nov 13, 7:25*pm, DLF1 wrote:
I'm trying to do a count if with 2 criteria - the difficulty I'm having is
the 2nd criteria involves a calculation - please see example below;

Table 1
Col A * * *Col B * * *
ABCD * * *20/10/2005

Table 2
Col A * * *Col B
ABCD * * *20/05/2005
ABCD * * *20/09/2005

I want to count if Col A from Table 2 matches A1 from Table 1 plus B1 from
Table 1 less Col B in Table 2 is greater than 30. I'm probably not explaining
this very well but the answer would be 1. My problem is I can't figure out
how to get avalue for B1 (Table 1) against every row in Col B in table 2. Am
I making any sense at all?

=SUMPRODUCT((Table2Col1=A1)*((B1-Table2Col2)30))

Replace Table2Col1/2 with the actual cell addresses, like $G$2:$G$2000 and $H$2:$H$2000

HTH,
Bernie
MS Excel MVP


"DLF1" wrote in message
...
I'm trying to do a count if with 2 criteria - the difficulty I'm having is
the 2nd criteria involves a calculation - please see example below;

Table 1
Col A Col B
ABCD 20/10/2005

Table 2
Col A Col B
ABCD 20/05/2005
ABCD 20/09/2005

I want to count if Col A from Table 2 matches A1 from Table 1 plus B1 from
Table 1 less Col B in Table 2 is greater than 30. I'm probably not explaining
this very well but the answer would be 1. My problem is I can't figure out
how to get avalue for B1 (Table 1) against every row in Col B in table 2. Am
I making any sense at all?

Maybe this...

=SUMPRODUCT(--(J1:J10=A1),--(B1-K1:K1030))

--
Biff
Microsoft Excel MVP


"DLF1" wrote in message
...
I'm trying to do a count if with 2 criteria - the difficulty I'm having is
the 2nd criteria involves a calculation - please see example below;

Table 1
Col A Col B
ABCD 20/10/2005

Table 2
Col A Col B
ABCD 20/05/2005
ABCD 20/09/2005

I want to count if Col A from Table 2 matches A1 from Table 1 plus B1 from
Table 1 less Col B in Table 2 is greater than 30. I'm probably not
explaining
this very well but the answer would be 1. My problem is I can't figure out
how to get avalue for B1 (Table 1) against every row in Col B in table 2.
Am
I making any sense at all?