Formula to Sum Based on Criteria

[k3yn0t3]

Hi there. I am trying to write a formula to copy across in the highlighted neon yellow cells that checks to see if 10 different members' info matches the filter criteria at the top of the sheet.

Should I add a ghost column underneath with same number of rows, and have each cell check each criteria, and yield a "Yes/No" depending? Then, I can have the formula in the neon yellow cell be "1" or "On" only if the sum of the ghost cells is 14 (i.e., Member [#]'s info met all 14 criteria)?

Appreciate any thoughts on this.

Best regards,
Sam

Factories_v20.xlsx
	A	B	C	D	E	F	G	H	I	J	K
5	Filters
6
7	Status	Open
8	Birth Date	1/1/1990	12/31/2019
9	Deceased	1/1/1990	12/31/2019
10	State	Florida, Illinois
11	Region	NY/NJ/PA, Other
12	Format	na
13	Building Type	Georgian
14	Ownership	Leased
15	Building Size	--	43,830
16	Yard Size	--	10,000
17	Total SQFT	--	43,830
18	Membership Expiration	1/1/1990	12/31/2019
19	Membership End w/ Extensions	1/1/1990	12/31/2019
20	Membership Fees	100	50,000
21
22
23
24	Members
25
26	Include?	On	On	Off	Off	Off	Off	Off	Off	Off	Off
27
28	Member Number	1	2	3	4	5	6	7	8	9	10
29	Status	Open	Open	Open	Open	Closed	Open	Open	Open	Open	Open
30	Birth Date	10/1/1988	2/1/1990	3/1/1991	2/1/1990	1/1/2015	1/1/2016	1/1/2017	1/1/2018	1/1/2019	1/1/2019
31	Deceased	na	na	na	na	7/23/2018	na	na	na	na	na
32	State	Connecticut	Florida	New York	Hawaii	Florida	Florida	Florida	Florida	Florida	Florida
33	Region	Florida	Florida	Florida	Florida	Florida	Florida	Florida	Florida	Florida	Florida
34	Format	na	na	na	na	na	na	na	na	na	na
35	Building Type	Colonial	Georgian	Industiral	Colonial	Georgian	Industiral	Colonial	Georgian	Industiral	Industiral
36	Ownership	Owned	Leased	Leased	Leased	Leased	Leased	Leased	Leased	Leased	Owned
37	Building Size	5,850	9,000	9,000	9,011	10,800	10,500	9,150	10,749	11,646	7,668
38	Yard Size	--	--	990	--	--	--	--	2,768	--	--
39	Total SQFT	5,850	9,000	9,990	9,011	10,800	10,500	9,150	13,517	11,646	7,668
40	Membership Expiration	12/31/2028	3/31/2020	9/30/2021	5/31/2023	na	6/30/2028	12/31/2023	9/30/2024	8/31/2020	1/14/2021
41	Membership End w/ Extensions	12/31/2028	3/31/2025	9/30/2030	5/31/2028	na	6/30/2028	12/31/2023	9/30/2029	8/31/2020	1/14/2021
42	Membership Fees	10555	10055	9555	9055	8555	8055	7555	7055	6555	6055
xl2bb

Cell Formulas
Range	Formula
C42:K42	C42	=+B42-500

 

[BigBeachBananas]

Building size, Yard Size, & Total SQFT don't match for the first 2 members. Why are they ON?

 

[k3yn0t3]

Building size, Yard Size, & Total SQFT don't match for the first 2 members. Why are they ON?

YES / NOs shown currently in neon yellow cells are merely hardcoded placeholders.

 

[BigBeachBananas]

YES / NOs shown currently in neon yellow cells are merely hardcoded placeholders.

Try this.

Book2
	B	C	D	E	F	G	H	I	J	K
22	Off	Off	Off	Off	Off	Off	Off	Off	Off	Off
Sheet9

Cell Formulas
Range	Formula
B22:K22	B22	=IF(SUM(--($B$3:$B$16=B25:B38))=14,"On","Off")

 

[k3yn0t3]

'^Thanks, BBB. Did you mean to post the other cells with the formulas checking for criteria? I'm only seeing the one row

 

[BigBeachBananas]

'^Thanks, BBB. Did you mean to post the other cells with the formulas checking for criteria? I'm only seeing the one row

You can paste the formula in cell B22 and drag it across to the right in your sheet but here's the entire sheet.

EDIT: Didn't realize you're starting at row 5. Adjust references as needed.

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Filters
2
3	Status	Open			On	TRUE
4	Birth Date	10/1/88	12/31/19			TRUE
5	Deceased	na	12/31/19			TRUE
6	State	Connecticut				TRUE
7	Region	Florida				TRUE
8	Format	na				TRUE
9	Building Type	Colonial				TRUE
10	Ownership	Owned				TRUE
11	Building Size	5850	43830			TRUE
12	Yard Size	0	10000			TRUE
13	Total SQFT	5850	43830			TRUE
14	Membership Expiration	12/31/28	12/31/19			TRUE
15	Membership End w/ Extensions	12/31/28	12/31/19			TRUE
16	Membership Fees	10555	50000			TRUE
17
18
19
20	Members
21
22	Include?	On	Off	Off	Off	Off	Off	Off	Off	Off	Off
23
24	Member Number	1	2	3	4	5	6	7	8	9	10
25	Status	Open	Open	Open	Open	Closed	Open	Open	Open	Open	Open
26	Birth Date	10/1/88	2/1/90	3/1/91	2/1/90	1/1/15	1/1/16	1/1/17	1/1/18	1/1/19	1/1/19
27	Deceased	na	na	na	na	7/23/18	na	na	na	na	na
28	State	Connecticut	Florida	New York	Hawaii	Florida	Florida	Florida	Florida	Florida	Florida
29	Region	Florida	Florida	Florida	Florida	Florida	Florida	Florida	Florida	Florida	Florida
30	Format	na	na	na	na	na	na	na	na	na	na
31	Building Type	Colonial	Georgian	Industiral	Colonial	Georgian	Industiral	Colonial	Georgian	Industiral	Industiral
32	Ownership	Owned	Leased	Leased	Leased	Leased	Leased	Leased	Leased	Leased	Owned
33	Building Size	5850	9000	9000	9010.5	10800	10500	9150	10749	11646	7668
34	Yard Size	0	0	990	0	0	0	0	2767.5	0	0
35	Total SQFT	5850	9000	9990	9010.5	10800	10500	9150	13516.5	11646	7668
36	Membership Expiration	12/31/28	3/31/20	9/30/21	5/31/23	na	6/30/28	12/31/23	9/30/24	8/31/20	1/14/21
37	Membership End w/ Extensions	12/31/28	3/31/25	9/30/30	5/31/28	na	6/30/28	12/31/23	9/30/29	8/31/20	1/14/21
38	Membership Fees	10555	-500	-500	-500	-500	-500	-500	-500	-500	-500
Sheet9

Cell Formulas
Range	Formula
E3	E3	=IF(SUM(--(B3:B16=$B$25:$B$38))=14,"On","Off")
F3:F16	F3	=(B3:B16=$B$25:$B$38)
B22:K22	B22	=IF(SUM(--($B$3:$B$16=B25:B38))=14,"On","Off")
C38:K38	C38	=+B42-500
Dynamic array formulas.

 

[k3yn0t3]

Understood, but I don't have any other cells currently in my excel. Does your formula assume I have the ghost cells with the checks?

 

[BigBeachBananas]

Understood, but I don't have any other cells currently in my excel. Does your formula assume I have the ghost cells with the checks?

If you look at the formula, I assumed there are 14 criteria. I didn't need a ghost cell for value 14.

 

[k3yn0t3]

Ah but does this check each Member individually against the parameters in the "filter" cells A1:C16?

Also, the two cells in columns B&C rows 4,5,11-16 are meant to represent a range during which a given member's number or date should fall. How could I check for that?

Thank you, BBB. This is a massive help. Been struggling w this for a few days and need to apply this to the cohort of ~200 members. I will use these "Yes / No" cells for each Member to determine whether or not i include that Member's underlying excel sheet data in a big Summary sheet

 

[BigBeachBananas]

Also, the two cells in columns B&C rows 4,5,11-16 are meant to represent a range during which a given member's number or date should fall. How could I check for that?

This part wasn't clear from post#1. I'll look at it again when I have more time.