0

I can't seem to figure out if what is wrong with this statement. There's no error shown but if I manually calculate the values, I get a different number than the statement.

I want to count how many cells are greater than zero in

  • Round 1 (tab) Column G and J and M and P and
  • in Round 2 (tab) Column G and J and M and P and
  • in Round 3 (tab) Column G and J and M and P

COUNTIFS('Round 1'!G2:G107, ">0", 'Round 1'!J2:J107, ">0", 'Round 1'!M2:M107, ">0", 'Round 1'!P2:P107, ">0", 'Round 2'!G2:G107, ">0", 'Round 2'!J2:J107, ">0", 'Round 2'!M2:M107, ">0", 'Round 2'!P2:P107, ">0", 'Round 3 '!G2:G107, ">0", 'Round 3 '!J2:J107, ">0", 'Round 3 '!M2:M107, ">0", 'Round 3 '!P2:P107, ">0")

Thank in advance for any help you're able to offer.

Shawn
  • 633
  • 1
  • 6
  • 13

1 Answers1

0

I think this is because the COUNTIFS will only count up rows where columns G,J,M & P are all >0 in each of the 3 sheets.

If you just want to count the number of cells across all the ranges that are >0 then try:

=COUNTIF({'Round 1'!G2:G107;'Round 1'!J2:J107;'Round 1'!M2:M107;'Round 1'!P2:P107;'Round 2'!G2:G107;'Round 2'!J2:J107;'Round 2'!M2:M107;'Round 2'!P2:P107;'Round 3'!G2:G107;'Round 3'!J2:J107;'Round 3'!M2:M107;'Round 3'!P2:P107},">0")

This formula creates a single stack array made up of all your separate ranges, and then counts the number of cells >0

Chris Hick
  • 3,004
  • 1
  • 13
  • 15