Urgent Help re complex if formula.

[khalid79m]

=COUNTIF(CallData!$E:$E,"NO CALLS")

this formula looks at sheet call data coloumn e and counts the word no calls.

I now need a formula that

will look at sheet "call data" coloum I and see how many names in that coloum match the name on sheet "otherdata" cell D26 and then workout howmany of those matched names have "No calls" written in coloum I in sheet "call Data". I had the formula before but erased it and finding it so difficult to build again

[robert111]

make a sumproduct table instead...

=sumproduct((e2:e1000="no calls")*(i2:i1000=$d$26))

obviously you will need to insert your own sheet names to the cell references.

I find sumproduct much better than pivot tables.

[khalid79m]

sorry for the slow response, thanks for your help, i know this maybe a dumb question but how do i add the sheet references.

[khalid79m]

sorry for the slow response, thanks for your help, i know this maybe a dumb question but how do i add the sheet references.

[Maistrye]

If you have a range specified, say F4:G12, and it's on another sheet, you put the sheet name in front of the range as follows (with single quotes before and after the sheet name, and an ! separating the name and the range:

'Another Sheet'!F4:G12

You could use it like this:

=COUNTIF('Another Sheet'!F4:G12,"Something")

Scott

[khalid79m]

Thanks mate works a treat