0

I have numeric data in column P in 50 different sheets ( each sheet has different person name). I have one summary Sheet which has names of all the worksheets in column A. In summary sheet column B against the name, i want to take count from each sheet. The criteria for count is to count all cells (in column P of individual sheets) which are having value > 0 or value < 0. IT should not count blank or zero values.

Improve this question

1 Answer 1

Reset to default
1

Why not a countifs

=COUNTIFS(P1:P10,"<>0",P1:P10,"<>"))

Throw in some indirect like this:

=COUNTIFS(INDIRECT("'"&A1&"'!$P$1:$P$10"),"<>",INDIRECT("'"&A1&"'!$P$1:$P$10"),"<>0")

and just drag it down.

Improve this answer
2
  • Thanks...partly resolved. But i still i am not able to refer to the data in various sheets. I have all sheet names in column A of summary sheet..... how do i refer that ?
    – user283476
    Commented Dec 20, 2013 at 14:22
  • @user283476 updated for the indirect, sorry!
    – Raystafarian
    Commented Dec 20, 2013 at 14:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .