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?

 

[PatRichard]

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

They are empty. They have some data validation criteria and some input messages to describe what I'm looking for.

 

[Cubist]

Try this:

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

 

[PatRichard]

Try this:

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

Ah - that seems to work. I'll have to read up on TOCOL and the double hyphen a bit to understand it. Thanks!

 

[Cubist]

The TOCOL and 1 remove the empty cells. The -- coerces the booleans into numbers 0's and 1's. On second thought, this method is not foolproof. Would you ever have a situation when one cell is empty like this (see row 8 and 9)?

Book1
	B	C	D
1	Number of Users	Shared Devices
2	4	2
3	6	5
4	188	31
5	0	0
6	13	27
7	0	9
8	0		< Empty
9		4	< Empty
10	2	0
11	0	3
12	0	7
13	0	12
14	2	2
15	4	4
16	0	1
17	5	6
18	5	2
19	5	0
20	104	39
21	18	3
22	39	7
23	0	0
Sheet2

 

[PatRichard]

The TOCOL and 1 remove the empty cells. The -- coerces the booleans into numbers 0's and 1's. On second thought, this method is not foolproof. Would you ever have a situation when one cell is empty like this (see row 8 and 9)?

Book1
	B	C	D
1	Number of Users	Shared Devices
2	4	2
3	6	5
4	188	31
5	0	0
6	13	27
7	0	9
8	0		< Empty
9		4	< Empty
10	2	0
11	0	3
12	0	7
13	0	12
14	2	2
15	4	4
16	0	1
17	5	6
18	5	2
19	5	0
20	104	39
21	18	3
22	39	7
23	0	0
Sheet2

Ideally, no. But customers fill this out and send it back to me for review. So I would question the empty cells and adjust as needed.

 

[Alex Blakenburg]

What constitutes an empty row. Is there a specific column that if empty means the row is empty ?
The below is Cubist formula which considers the row empty if BOTH the value columns are empty.

=SUM((SiteInfo[Number of Users]+SiteInfo[Shared Devices]<Data!E51)*((SiteInfo[Number of Users]&SiteInfo[Shared Devices])<>""))

 

[PatRichard]

What constitutes an empty row. Is there a specific column that if empty means the row is empty ?
The below is Cubist formula which considers the row empty if BOTH the value columns are empty.

=SUM((SiteInfo[Number of Users]+SiteInfo[Shared Devices]<Data!E51)*((SiteInfo[Number of Users]&SiteInfo[Shared Devices])<>""))

I'll play with that. We tell the customer that no cells in the workbook should be empty for any site (row) containing data.

 

[Cubist]

I'll play with that. We tell the customer that no cells in the workbook should be empty for any site (row) containing data.

Alex's formula accounts for the situation mentioned in post#14 when 1 cell is empty. It would count those rows as FALSE.

 

[Alex Blakenburg]

Alex's formula accounts for the situation mentioned in post#14 when 1 cell is empty. It would count those rows as FALSE.

Not sure if I am misinterpreting what you are saying but that formula will count it if either of the value columns are not blank.

@PatRichard if you want us to ignore the row if either of the rows are blank just let us know but then you might want another count formula to flag that you have some that are only partially complete.

 

[PatRichard]

Not sure if I am misinterpreting what you are saying but that formula will count it if either of the value columns are not blank.

@PatRichard if you want us to ignore the row if either of the rows are blank just let us know but then you might want another count formula to flag that you have some that are only partially complete.

This should be sufficient. I use some other methods to flag empty cells, etc. I appreciate everyone's help.