0

I am trying to use a count if function that will count -1 where it exists in each row.

The problem is, i may sometimes have 1, 2 or 3 instances of -1 in any one row. However, i only want to count the first instance of -1 for each row?

=COUNTIF(G11:AK500,"-1")

Please can someone show me how to do this?

Thanks in advance

G_Man97
  • 37
  • 2
  • 7
  • Use a helper column; in AL11 put `=COUNTIF(G11:AK11,"-1")>0` and copy down. Then do: `=COUNTIF(AL11:AL500,True)` – Scott Craner Feb 25 '16 at 14:52
  • @ScottCraner thanks but problem with this is i have over 1000 rows, and using a separate count function for each row might not be ideal – G_Man97 Feb 25 '16 at 14:57
  • Interesting, I do this method on tables that have 30,000 rows and yes there is a noticeable calc time, but it is less than a second. I will hang around and see if someone smarter than me, and there are a lot of those, can find a single formula method. I would also be very interested in that. – Scott Craner Feb 25 '16 at 15:06
  • Not sure if it's faster than @ScottCraner's suggestion, but what about `{=--OR(G11:AK11=-1)}` – JasonAizkalns Feb 25 '16 at 15:27

2 Answers2

0

You may also try NOT(ISERROR(MATCH("-1";G11:AK11;0))), but I don't know if it makes any calculation time difference

powiedz
  • 11
  • 1
0

Have you tried to use combination of IF and COUNT? As far as I understand, counting the first instance of "-1" will give you the answer 1. So, when you could try something like this 

=IF(COUNTIF(G11:AK500,"-1")>0,1,0)

That should give you a lot of creativity to decide what you're going to get from such count.
Plus doing this over 1000 rows shouldn't slow the spreadsheet that much, so it's worth a try. If you don't like dragging the formula across so many rows, consider, if possible, converting the range to a table, which makes life with large spreadsheets a lot easier

Dani El
  • 173
  • 9
  • This does not count the number of rows that have at least 1 `-1`, it only returns 1 if there is a `-1` or more in the whole range. – Scott Craner Feb 25 '16 at 15:33
  • If you count it this way and sum up the answers, you will get the number of rows with "-1". – Dani El Feb 25 '16 at 15:38