How to find product of every other cell

[ACSKNIGHTS]

Hello, I am trying to figure out a formula to count True/False check mark boxes in every other column without having to select every other cell in that row. I have multiple rows for a roster of students with enough columns for every school day (roughly 260) on our school calendar where we will be checking boxes according to whether or not the student ate hot lunch (one column) and if they had an extra meal (next column) for each day. I need to keep a running tally of the two separate values for the entire range but they have to be side by side, therefore I need to count every other column. Is there a shorter way to do this other than adding 260 'Countifs' formulas together? Thanks for your feedback!

 

[awoohaw]

i'm not sure how to deal wiht check boxes, but here is what you need to do if check box values are 1's and 0's:

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		HotDay1	ExtraDay1	HotDay2	ExtraDay2	HotDay3	ExtraDay3	HotDay4	ExtraDay4	HotDay5	ExtraDay5	HotDay6	ExtraDay6	HotTotal	HotExtra
2	sally	1	0	1	0	0	0	0	1	1	0	1	1	4	2
3	John	1	1	0	1	1	0	0	0	0	0	0	1	2	3
Sheet10

Cell Formulas
Range	Formula
N2:N3	N2	=SUM((--(ISEVEN(COLUMN(B2:M2))))*(B2:M2))
O2:O3	O2	=SUM((--(ISODD(COLUMN(B2:M2))))*(B2:M2))

 

[kweaver]

Welcome to the Forum. You'll get better responses if you post some of your data using XL2BB (and not an image)..

Your subject says "product" but I think you mean "sum".

Without seeing your sample data, here are 2 options (adjust the last column reference in the total columns accordingly):

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Student	Total Hot	Total Extra	hot	extra	hot	extra	hot	extra	hot	extra	hot	extra	hot	extra
2	AAAAA	4	3	0	0	1	0	1	1	0	1	0	0	1	0	1	1
3	BBBBB	2	4	0	1	0	0	1	0	0	1	0	1	1	1	0	0
4	CCCCC	2	5	0	1	0	1	0	1	0	1	1	0	0	1	1	0
5	DDDD	1	5	0	1	0	0	1	0	0	1	0	1	0	1	0	1
6
7
8	Student	Total Hot	Total Extra	hot	extra	hot	extra	hot	extra	hot	extra	hot	extra	hot	extra	hot	extra
9	AAAAA	4	3	NO	NO	YES	NO	YES	YES	NO	YES	NO	NO	YES	NO	YES	YES
10	BBBBB	2	4	NO	YES	NO	NO	YES	NO	NO	YES	NO	YES	YES	YES	NO	NO
11	CCCCC	2	5	NO	YES	NO	YES	NO	YES	NO	YES	YES	NO	NO	YES	YES	NO
12	DDDD	1	5	NO	YES	NO	NO	YES	NO	NO	YES	NO	YES	NO	YES	NO	YES
Sheet5

Cell Formulas
Range	Formula
B2:B5	B2	=SUMPRODUCT((MOD(COLUMN($D1:$Q1),2)=0)*($D2:$Q2))
C2:C5	C2	=SUMPRODUCT((MOD(COLUMN($D1:$Q1),2)=1)*($D2:$Q2))
B9:B12	B9	=SUMPRODUCT((MOD(COLUMN($D8:$Q8),2)=0)*($D9:$Q9="YES"))
C9:C12	C9	=SUMPRODUCT((MOD(COLUMN($D8:$Q8),2)=1)*($D9:$Q9="YES"))

 

[awoohaw]

i'm not sure how to deal wiht check boxes, but here is what you need to do if check box values are 1's and 0's:

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		HotDay1	ExtraDay1	HotDay2	ExtraDay2	HotDay3	ExtraDay3	HotDay4	ExtraDay4	HotDay5	ExtraDay5	HotDay6	ExtraDay6	HotTotal	HotExtra
2	sally	1	0	1	0	0	0	0	1	1	0	1	1	4	2
3	John	1	1	0	1	1	0	0	0	0	0	0	1	2	3
Sheet10

Cell Formulas
Range	Formula
N2:N3	N2	=SUM((--(ISEVEN(COLUMN(B2:M2))))*(B2:M2))
O2:O3	O2	=SUM((--(ISODD(COLUMN(B2:M2))))*(B2:M2))

if you get an error using SUM, it will also work with SUMPRODUCT.

 

[ACSKNIGHTS]

i'm not sure how to deal wiht check boxes, but here is what you need to do if check box values are 1's and 0's:

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		HotDay1	ExtraDay1	HotDay2	ExtraDay2	HotDay3	ExtraDay3	HotDay4	ExtraDay4	HotDay5	ExtraDay5	HotDay6	ExtraDay6	HotTotal	HotExtra
2	sally	1	0	1	0	0	0	0	1	1	0	1	1	4	2
3	John	1	1	0	1	1	0	0	0	0	0	0	1	2	3
Sheet10

Cell Formulas
Range	Formula
N2:N3	N2	=SUM((--(ISEVEN(COLUMN(B2:M2))))*(B2:M2))
O2:O3	O2	=SUM((--(ISODD(COLUMN(B2:M2))))*(B2:M2))

I might have to consider changing it to 0's/1's. Thank you for your help!

 

[ACSKNIGHTS]

if you get an error using SUM, it will also work with SUMPRODUCT.

Thank you so much! I might try the 0's/1's. Appreciate the help!

 

[ACSKNIGHTS]

Welcome to the Forum. You'll get better responses if you post some of your data using XL2BB (and not an image)..

Your subject says "product" but I think you mean "sum".

Without seeing your sample data, here are 2 options (adjust the last column reference in the total columns accordingly):

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Student	Total Hot	Total Extra	hot	extra	hot	extra	hot	extra	hot	extra	hot	extra	hot	extra
2	AAAAA	4	3	0	0	1	0	1	1	0	1	0	0	1	0	1	1
3	BBBBB	2	4	0	1	0	0	1	0	0	1	0	1	1	1	0	0
4	CCCCC	2	5	0	1	0	1	0	1	0	1	1	0	0	1	1	0
5	DDDD	1	5	0	1	0	0	1	0	0	1	0	1	0	1	0	1
6
7
8	Student	Total Hot	Total Extra	hot	extra	hot	extra	hot	extra	hot	extra	hot	extra	hot	extra	hot	extra
9	AAAAA	4	3	NO	NO	YES	NO	YES	YES	NO	YES	NO	NO	YES	NO	YES	YES
10	BBBBB	2	4	NO	YES	NO	NO	YES	NO	NO	YES	NO	YES	YES	YES	NO	NO
11	CCCCC	2	5	NO	YES	NO	YES	NO	YES	NO	YES	YES	NO	NO	YES	YES	NO
12	DDDD	1	5	NO	YES	NO	NO	YES	NO	NO	YES	NO	YES	NO	YES	NO	YES
Sheet5

Cell Formulas
Range	Formula
B2:B5	B2	=SUMPRODUCT((MOD(COLUMN($D1:$Q1),2)=0)*($D2:$Q2))
C2:C5	C2	=SUMPRODUCT((MOD(COLUMN($D1:$Q1),2)=1)*($D2:$Q2))
B9:B12	B9	=SUMPRODUCT((MOD(COLUMN($D8:$Q8),2)=0)*($D9:$Q9="YES"))
C9:C12	C9	=SUMPRODUCT((MOD(COLUMN($D8:$Q8),2)=1)*($D9:$Q9="YES"))

Thank you for the feedback. I will try to post a photo next time. Those two options are definitely worth considering...the check boxes are just so quick and convenient, but we might have to consider a numerical or text option. Thank you for your help!

 

[awoohaw]

do not post photos if you can post a table of data. A mini worksheet with the xl2bb add in is BY FAR the most effective way to get help from the forum.
photos do almost nothing.

Thank you for the feedback. I will try to post a photo next time. Those two options are definitely worth considering...the check boxes are just so quick and convenient, but we might have to consider a numerical or text option. Thank you for your help!

 

[awoohaw]

I think check boxes send values to associated cells, but that mean you need to create a lot of check boxes.

If you do, you just need to know what the checkbox values are and what column they go in.

 

[kweaver]

Thank you for the feedback. I will try to post a photo next time. Those two options are definitely worth considering...the check boxes are just so quick and convenient, but we might have to consider a numerical or text option. Thank you for your help!

How are the check boxes inserted in the cells? Are they from a Form Control or ActiveX?

Please don't post a photo...use XL2BB.