Help with totals list based on criteria (COUNTIF?)

[Guitarmageddon]

Hello all,
Im having trouble figuring out what is probably a simple counting function. I have a weight lifting log that I've made for myself. Within it, I classify the various lifts underneath the broader muscle groups they fall under. For example, squats work the "quads" and a row may work the "back" and so forth. I have a totals list that is meant to count all the sets I do during a week, at the bottom starting in row 48.

Columns H:M are the recorded reps for each set that I do.

All I really need to do is for the list at the bottom to reference the muscle group name, check column B for that name, and then count any non-blank cell for that corresponding lift. It should look across the training days of that week.

For example, in the picture attached, QUAD should count up to be 14, because I have recorded 14 completed sets across the days. Better yet, in case I get lazy and record text notes in any of those cells rather than my designated notes column, can we make it just count cells that are NUMBERS only? In my head this is like some countif/vlook type thing....

Thanks for any help.

 

[AlanY]

could you explain how do you get to the 14 in your example, i.e. cells that added up to it.

 

[Guitarmageddon]

could you explain how do you get to the 14 in your example, i.e. cells that added up to it.

Hello! Yes, so at the bottom, looking for the totals of all QUAD workouts.... the columns within my blue SETS area (H thru M) have multiple days which I performed QUAD lifts.

So finding QUAD within day 1 thru 7 in column B, I then look over and see that I performed a count of 14 total sets. The actual repetition number within those cells is irrelevant to me. I just want to count how many sets were performed. So that would be rows 6 (3 sets), 7 (4 sets) , 25 (3 sets), and row 26 (4 sets). Does this make sense?

 

[AlanY]

an easy way is to use a helper column like this

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Day 1	Quad			10				X		1
2					8				X		1
3									X		0
4									X		0
5									X		0
6	Day 2								X		0
7	Day 3								X		0
8	Day 4	Quad			8	8			X		2
9	Day 5								X		0
10	Day 6								X		0
11	Day 7	Quad						12	X		1
12
13
14
15
16
17		Quad	4
Sheet2

Cell Formulas
Range	Formula
K1:K11	K1	=COUNT(E1:J1)
C17	C17	=SUMPRODUCT(K1:K11*--(B1:B11=B17))

 

[Mansel]

Entered where you wish the results to be this would work.

=SUMIF(B6:B42,"QUAD",H6:H42)+SUMIF(B6:B42,"QUAD",I6:I42)+SUMIF(B6:B42,"QUAD",J6:J42)+SUMIF(B6:B42,"QUAD",K6:K42)+SUMIF(B6:B42,"QUAD",L6:L42)+SUMIF(B6:B42,"QUAD",M6:M42)

 

[Guitarmageddon]

Entered where you wish the results to be this would work.

=SUMIF(B6:B42,"QUAD",H6:H42)+SUMIF(B6:B42,"QUAD",I6:I42)+SUMIF(B6:B42,"QUAD",J6:J42)+SUMIF(B6:B42,"QUAD",K6:K42)+SUMIF(B6:B42,"QUAD",L6:L42)+SUMIF(B6:B42,"QUAD",M6:M42)

Hello, this does work, but it is rather summing the total of repetitions that I record, rather than purely counting each box as ONE set. Does that make sense? So its basically asking "are there numbers within the cell? if so, thats one set performed" and Im interesting in simply counting those sets.

 

[Guitarmageddon]

I ended up going with the helper column from @AlanY for now. Its nice to have it all rolled up into one though, maybe because of the challenge haha

 

[Mansel]

Hi,

Makes perfect sense, my bad for misinterpreting your desired result. As long as you have it all sorted now.

 

[Guitarmageddon]

Hi,

Makes perfect sense, my bad for misinterpreting your desired result. As long as you have it all sorted now.

Thanks a lot. Is this something that an array formula would have been able to wrap into one clean cell? I am not familiar with those and thy are a bit intimidating. I should familiarize myself with those however. My brain power has been so geared on understanding power pivot these days!

 

[AlanY]

I ended up going with the helper column from @AlanY for now. Its nice to have it all rolled up into one though, maybe because of the challenge haha

another way without the helper column

Book1
	A	B	C	D	E	F	G	H	I	J
1	Day 1	Quad			10				X
2					8				X
3									X
4									X
5									X
6	Day 2								X
7	Day 3								X
8	Day 4	Quad			8	8			X
9	Day 5								X
10	Day 6								X
11	Day 7	Quad						12	X
12
13
14
15
16
17		Quad	4
Sheet1

Cell Formulas
Range	Formula
C17	C17	=SUMPRODUCT((B1:B11=B17)*(E1:J11>0)*(ISNUMBER(E1:J11)))