Countif using totals of two named columns

[PatRichard]

I'm trying to use countif to figure out how many rows have two cells that added together are greater than another value. I know for a single column, something like

=COUNTIF(SiteInfo[Number of Users],"<"&Data!E51)

works fine. But I want the sum of SiteInfo[Number of Users] + SiteInfo[Shared Devices] as the criteria, and just can't seem to find something that works. The closest I've come is counting the number of rows where either cell is < Data!E51. Thoughts?

 

[Cubist]

Try:

=SUM(--(SiteInfo[Number of Users] + SiteInfo[Shared Devices] <Data!E51))

 

[PatRichard]

No, that's not valid. I need to know the number of rows (countif) where the total of the two cells is < Data!E51 (currently 25).

 

[Cubist]

The formula does exactly that. Did you try it? If you get result different than what you're expecting can you post a sample data in a copyable format for testing?

 

[Alex Blakenburg]

two cells that added together are greater than another value.

Your formula says less than but your words are greater than. If you reverse the sign on Cubist's formula do you get the number you want then ?

 

[PatRichard]

The formula does exactly that. Did you try it? If you get result different than what you're expecting can you post a sample data in a copyable format for testing?

I got 95 when testing your formula with a sample of 22 rows. It should be 18.

Site name	Number of Users	Shared Devices
Airport	4​	2​
Animal Control	6​	5​
City Hall	188​	31​
Tudor Pump Station	0​	0​
Fire Station 1 (HQ)	13​	27​
Fire Station 2	0​	9​
Fire Station 3	0​	10​
Fire Station 4	0​	4​
Fire Station 4 (old)	2​	0​
Fire Station 5	0​	3​
Fire Station 6	0​	7​
Fire Station 7	0​	12​
Community Center	2​	2​
Community Center & Waves	4​	4​
Park	0​	1​
Community Center & Ampitheatre	5​	6​
Community Center 2	5​	2​
Parks Maintenance	5​	0​
Police & Courts	104​	39​
Public Works Operations & Fleet Maintenance	18​	3​
Water Department	39​	7​
Water Operations (old)	0​	0​

 

[PatRichard]

Your formula says less than but your words are greater than. If you reverse the sign on Cubist's formula do you get the number you want then ?

My bad. It should be less than 25.

 

[Cubist]

I am getting 18.

Book1
	A	B	C	D	E
1	Site name	Number of Users	Shared Devices
2	Airport	4	2		18
3	Animal Control	6	5
4	City Hall	188	31
5	Tudor Pump Station	0	0
6	Fire Station 1 (HQ)	13	27
7	Fire Station 2	0	9
8	Fire Station 3	0	10
9	Fire Station 4	0	4
10	Fire Station 4 (old)	2	0
11	Fire Station 5	0	3
12	Fire Station 6	0	7
13	Fire Station 7	0	12
14	Community Center	2	2
15	Community Center & Waves	4	4
16	Park	0	1
17	Community Center & Ampitheatre	5	6
18	Community Center 2	5	2
19	Parks Maintenance	5	0
20	Police & Courts	104	39
21	Public Works Operations & Fleet Maintenance	18	3
22	Water Department	39	7
23	Water Operations (old)	0	0
Sheet2

Cell Formulas
Range	Formula
E2	E2	=SUM(--(B2:B23+C2:C23<25))

 

[PatRichard]

I am getting 18.

I see what's happening. The table has 100 rows total. a bunch of which are empty rows, so those are getting counted. So I'll need to work around that. Deleting the empty rows isn't going to be a solution. But at least you've got me headed in the right direction.

 

[Cubist]

I see what's happening. The table has 100 rows total. a bunch of which are empty rows, so those are getting counted. So I'll need to work around that. Deleting the empty rows isn't going to be a solution. But at least you've got me headed in the right direction.

Are the rows truly empty or are they empty strings "" e.g. returned from a formula?