How To Count # Sessions - Any Of 3 Cells Marked Per Row

[EdAROCNY]

I am recording my golf practice sessions. For each Day Of Month (1-31 rows) there are 3 columns - Long, Short, and Putting. In the Long & Short the number of golf balls hit is recorded. The time spent Putting is recorded. Practice on any day could include 1, 2, or all 3 categories. For example, there may only be putting practice - Long and Short are left blank and the time spent putting is entered in the Putting cell for the day. 1 practice day. Another day there may be Long and Putting - 2 categories, still counting as 1 practice day.

It would be easy to add a 4th column, "Session", and mark it with an "x", then do a COUNTA on the column.

Taking this opportunity to develop skills. Is it possible to do a COUNTIF such that if any one of the 3 columns has a value, then the day would count as a practice day.
Stuck at =COUNTIF(N2:P33,OR(COUNT(N2),COUNT(O2),COUNT(P2)))
Where N2:P33 - Range of 31 rows, 3 columns. N2 is cell in the first row of the Long column. O2 starts the Short column. P2 starts the Putting column.

Also tried =COUNTIF(N2:P33,SUM(N2:P2)>0)

 

[Gerald Higgins]

It would be easy to add a 4th column, "Session", and mark it with an "x", then do a COUNTA on the column.

That's what I would do.
You could write a formula to automatically input the "x", for example . . .
=if(sum(N2:O2)>0,"x","")

and then use COUNTIF on the results (not COUNTA because it will count the apparently blank cells too).

 

[Eric W]

Here are 3 ways to do that:

Book1
	A	B	C	D	E	F	G
1	Date	Long	Short	Putting	Any		# of practice days
2	1	10			X		8
3	2	10	20		X
4	3	10		30	X		8
5	4	10	20	30	X
6	5			25	X		8
7	6			15	X
8	7
9	8		45		X
10	9	10	30		X
11	10
Sheet5

Cell Formulas
Range	Formula
G2	G2	=COUNTIF(E2:E11,"X")
G4	G4	=SUMPRODUCT(--(B2:B11+C2:C11+D2:D11>0))
G6	G6	=SUMPRODUCT(SIGN(MMULT(--(B2:D11>0),{1;1;1})))
E2:E11	E2	=IF(COUNT(B2:D2),"X","")

The "best" way? The way that makes most sense to you. There's nothing wrong with adding a helper column if it makes it easier to comprehend.

 

[EdAROCNY]

Gerald - Thank you for that solution. It would remove the manual process of entering a value to denote the situation.

Eric - THANKS! This is why I posed the question - to learn something new! I like the G4 SUMPRODUCT solution. Had to look up what the functions does and what happens with the --.

One question, so that I completely understand and don't just "copy" in the future:
Looking at the formula =SUMPRODUCT(--(B2:B11+C2:C11+D2:D11>0))
The formula is processing each row, B2 * C2 * D2. Does the >0 eliminate any of the 3 cells with no value? (Because if any cells are blank, i.e. equals zero, the Product is zero.)

 

[Eric W]

In this case the PRODUCT part of SUMPRODUCT is not doing anything. That only happens when we have more than one condition, and we only have one. Still, using SUMPRODUCT is nice because it has built-in array processing, which is what we're doing with the B2:B11, C2:C11, D2:D11 ranges.

The B2:B11+C2:C11+D2:D11 is calculated first, so you have 10+0+0 = 10 (row 2), then 10+20+0=30 (row 3), etc., resulting in an array of {10;30;40;60;25;15;0;45;40;0}. Then the >0 part does the comparison and returns {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE}. Then as you looked up the "double unary" (--) converts the TRUE/FALSE to 1/0, giving {1;1;1;1;1;1;0;1;1;0}, and finally the SUM part adds those up.

If you want to see what the PRODUCT part does, imagine you only want to count odd days where you practice. The formula would be:

=SUMPRODUCT(--(B2:B11+C2:C11+D2:D11>0),--(MOD(A2:A11,2)=1))

You'd end up with 2 arrays calculated as before, then SUMPRODUCT would multiply them together, item by item. Only the items that are both 1 (1*1=1) would stay 1 and be counted.

There's a whole lot more to SUMPRODUCT than I can explain here, but there are lots of places on the Interweb that can explain more. Good luck!

 

[EdAROCNY]

Eric - Now I got it. I misread the explanation at Microsoft Support and did not pick up separating the arrays with + signs, versus commas (to multiply).
Thanks for taking the extra time to teach me. I appreciate it.