Help with COUNTIF Statements

[vanurdi010310]

I have the following statement which basically counts the number of telephone calls in a ranged (in sheet 2) based on cell 'D58'. This works perfectly.

=COUNTIF(Sheet2!L:L,D58)

I now need to add an IF statement or another COUNTIF statement to the formula so I can count the number of calls over 30 seconds. The length of the call is in Column N in Sheet 2.

I have no idea how to do this and would be grateful for any tips.

[daddylonglegs]

In Exel 2003 use SUMPRODUCT e.g.

=SUMPRODUCT((Sheet2!L$2:L$100=D58)*(Sheet2!N$2:N$100>30))

assumes column N data is shown as a number (of seconds) - if it's in time format change to

=SUMPRODUCT((Sheet2!L$2:L$100=D58)*(Sheet2!N$2:N$100>TIME(0,0,30)))

adjust ranges as required but you can't use the whole column

[Harribone]

Either:

=COUNTIF(Sheet2!L:L,D58)+COUNTIF(Sheet2!N:N,">30")

Or if you want calls over 30 seconds that have a coresponding value in Col L:

=COUNTIFS(Sheet2!L:L,D58,Sheet2!N:N,">30")

[Kevin UK]

@ Harribone

Might be worth pointing out, if they have Excel 2007 +, they could use COUNTIFS. As by their profile, Excel 2003! Mind you, that is nothing to go by!.

[Harribone]

Oops went straight in with a solution without realising what version this needed to work on!

[vanurdi010310]

Excel 2003 is being used

[vanurdi010310]

This statement is the one I'm after but it returns an error

=COUNTIF(Sheet2!L:L,D58,Sheet2!N:N,">30")

[Kevin UK]

Hi vanurdi010310

COUNTIF will not except multiple ranges and criteria.

[daddylonglegs]

Did you try my suggestion in post #2? - SUMPRODUCT is a convenient function to use in Excel 2003 for "multi-condition" counting or summing