Formula to Sum Based on Criteria

k3yn0t3

k3yn0t3

New Member
Joined
Oct 5, 2023
Messages
42
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJK
5Filters
6
7StatusOpen
8Birth Date1/1/199012/31/2019
9Deceased1/1/199012/31/2019
10StateFlorida, Illinois
11RegionNY/NJ/PA, Other
12Formatna
13Building TypeGeorgian
14OwnershipLeased
15Building Size--43,830
16Yard Size--10,000
17Total SQFT--43,830
18Membership Expiration1/1/199012/31/2019
19Membership End w/ Extensions1/1/199012/31/2019
20Membership Fees10050,000
21
22
23
24Members
25
26Include?OnOnOffOffOffOffOffOffOffOff
27
28Member Number12345678910
29StatusOpenOpenOpenOpenClosedOpenOpenOpenOpenOpen
30Birth Date10/1/19882/1/19903/1/19912/1/19901/1/20151/1/20161/1/20171/1/20181/1/20191/1/2019
31Deceasednananana7/23/2018nanananana
32StateConnecticutFloridaNew YorkHawaiiFloridaFloridaFloridaFloridaFloridaFlorida
33RegionFloridaFloridaFloridaFloridaFloridaFloridaFloridaFloridaFloridaFlorida
34Formatnananananananananana
35Building TypeColonialGeorgianIndustiralColonialGeorgianIndustiralColonialGeorgianIndustiralIndustiral
36OwnershipOwnedLeasedLeasedLeasedLeasedLeasedLeasedLeasedLeasedOwned
37Building Size5,8509,0009,0009,01110,80010,5009,15010,74911,6467,668
38Yard Size----990--------2,768----
39Total SQFT5,8509,0009,9909,01110,80010,5009,15013,51711,6467,668
40Membership Expiration12/31/20283/31/20209/30/20215/31/2023na6/30/202812/31/20239/30/20248/31/20201/14/2021
41Membership End w/ Extensions12/31/20283/31/20259/30/20305/31/2028na6/30/202812/31/20239/30/20298/31/20201/14/2021
42Membership Fees105551005595559055855580557555705565556055
xl2bb
Cell Formulas
RangeFormula
C42:K42C42=+B42-500
 
Last edited:
Sort by date Sort by votes
BigBeachBananas said:
This part wasn't clear from post#1. I'll look at it again when I have more time.
Click to expand...
Thank you again for taking a look. Very helpful. I have to send the workbook tomorrow a.m. (ET) so don't worry about it if you're tied up tonight. I'll post here immediately if I get it working at any point.
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
BigBeachBananas said:
This part wasn't clear from post#1. I'll look at it again when I have more time.
Click to expand...
Sounds great. I have to have this in by tomorrow late a.m. so no worries if you're tied up between now and then. Will post any progress i make here immediately in the meantime.
 
Upvote 0
How about this?

Book2
ABCDEFGHIJK
5Filters
6
7StatusOpen
8Birth Date1/1/199012/31/2019
9Deceased1/1/199012/31/2019
10StateFlorida, Illinois
11RegionNY/NJ/PA, Other
12Formatna
13Building TypeGeorgian
14OwnershipLeased
15Building Size043830
16Yard Size010000
17Total SQFT043830
18Membership Expiration1/1/199012/31/2019
19Membership End w/ Extensions1/1/199012/31/2019
20Membership Fees10050000
21
22
23
24Members
25
26Include?0000000000
27
28Member Number12345678910
29StatusOpenOpenOpenOpenClosedOpenOpenOpenOpenOpen
30Birth Date10/1/19882/1/19903/1/19912/1/19901/1/20151/1/20161/1/20171/1/20181/1/20191/1/2019
31Deceasednananana7/23/2018nanananana
32StateConnecticutFloridaNew YorkHawaiiFloridaFloridaFloridaFloridaFloridaFlorida
33RegionFloridaFloridaFloridaFloridaFloridaFloridaFloridaFloridaFloridaFlorida
34Formatnananananananananana
35Building TypeColonialGeorgianIndustiralColonialGeorgianIndustiralColonialGeorgianIndustiralIndustiral
36OwnershipOwnedLeasedLeasedLeasedLeasedLeasedLeasedLeasedLeasedOwned
37Building Size5850900090009010.51080010500915010749116467668
38Yard Size0099000002767.500
39Total SQFT5850900099909010.51080010500915013516.5116467668
40Membership Expiration12/31/20283/31/20209/30/20215/31/2023na6/30/202812/31/20239/30/20248/31/20201/14/2021
41Membership End w/ Extensions12/31/20283/31/20259/30/20305/31/2028na6/30/202812/31/20239/30/20298/31/20201/14/2021
42Membership Fees105551005595559055855580557555705565556055
Sheet1
Cell Formulas
RangeFormula
B26:K26B26=SUM(--(B29=$B$7)*(AND(B30:B31<=$C$8:$C$9,B30:B31>=$B$8:$B$9))*($B$10:$B$14=B32:B36)*(AND(B37:B42<=$B$15:$B$20,B37:B42>=$C$15:$C$20)))
C42:K42C42=+B42-500
 
Upvote 1
Edit the formula.

Excel Formula: 
=IF(SUM(--(B29=$B$7)*(AND(B30:B31<=$C$8:$C$9,B30:B31>=$B$8:$B$9))*($B$10:$B$14=B32:B36)*(AND(B37:B42>=$B$15:$B$20,B37:B42<=$C$15:$C$20)))=5,"On","Off")
 
Upvote 1
I have cleaned up the filtering cells / mechanism...do you think this might work?
Filters
StateStatusRegionBuilding SizeBuilding TypeMembership End w/ Extensions
FloridaOnOpenOnFloridaOnMin10,000GeorgianOnStart1/1/1990
GeorgiaOnClosedOnOtherOnMax50,000ColonialOnEnd1/1/2018
IllinoisOnNY/NJ/PAOnTudorOn
New YorkOn
PennsylvaniaOnBirthdayFormatYard SizeOwnershipMembership Expiration
NevadaOnStart1/1/1990naOnMin10,000OwnedOnStart1/1/1990
MassachusettsOnEnd1/1/1990LuxuryOnMax50,000LeasedOnEnd1/1/2018
New JerseyOnMid-MarketOn
MarylandOnDeathAffordableOnTotal SQFTMembership Fees
OhioOnStart1/1/1990MixedOnMin10,000Min$0
VirginiaOnEnd1/1/1990Max50,000Max$50,000
DelawareOn
TennesseeOn
Member Info
Include?#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!
Member Number
1​
2​
3​
4​
5​
6​
7​
8​
9​
10​
StatusOpenOpenOpenOpenClosedOpenOpenOpenOpenOpen
Birth Date10/1/19882/1/19903/1/19912/1/19901/1/20151/1/20161/1/20171/1/20181/1/20191/1/2019
Deceasednananana7/23/2018nanananana
StateConnecticutFloridaNew YorkHawaiiFloridaFloridaFloridaFloridaFloridaFlorida
RegionFloridaFloridaFloridaFloridaFloridaFloridaFloridaFloridaFloridaFlorida
Formatnananananananananana
Building TypeColonialGeorgianIndustiralColonialGeorgianIndustiralColonialGeorgianIndustiralIndustiral
OwnershipOwnedLeasedLeasedLeasedLeasedLeasedLeasedLeasedLeasedOwned
Building Size
5850​
9000​
9000​
9010.5​
10800​
10500​
9150​
10749​
11646​
7668​
Yard Size
0​
0​
990​
0​
0​
0​
0​
2767.5​
0​
0​
Total SQFT
5850​
9000​
9990​
9010.5​
10800​
10500​
9150​
13516.5​
11646​
7668​
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​
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​
Membership Fees
10555​
10055​
9555​
9055​
8555​
8055​
7555​
7055​
6555​
6055​
 
Upvote 0
I've cleaned up the filtering mechanism here...will this work better?

Factories_v23.xlsm
ABCDEFGHIJKLMNOPQ
3Filters
4
5StateStatusRegionBuilding SizeBuilding TypeMembership End w/ Extensions
6FloridaOnOpenOnFloridaOnMin10,000GeorgianOnStart1/1/1990
7GeorgiaOnClosedOnOtherOnMax50,000ColonialOnEnd1/1/2018
8IllinoisOnNY/NJ/PAOnTudorOn
9New YorkOn
10PennsylvaniaOnBirthdayFormatYard SizeOwnershipMembership Expiration
11NevadaOnStart1/1/1990naOnMin10,000OwnedOnStart1/1/1990
12MassachusettsOnEnd1/1/1990LuxuryOnMax50,000LeasedOnEnd1/1/2018
13New JerseyOnMid-MarketOn
14MarylandOnDeathAffordableOnTotal SQFTMembership Fees
15OhioOnStart1/1/1990MixedOnMin10,000Min$0
16VirginiaOnEnd1/1/1990Max50,000Max$50,000
17DelawareOn
18TennesseeOn
19
20
21Member Info
22
23Include?#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!#REF!
24
25Member Number12345678910
26StatusOpenOpenOpenOpenClosedOpenOpenOpenOpenOpen
27Birth Date10/1/19882/1/19903/1/19912/1/19901/1/20151/1/20161/1/20171/1/20181/1/20191/1/2019
28Deceasednananana7/23/2018nanananana
29StateConnecticutFloridaNew YorkHawaiiFloridaFloridaFloridaFloridaFloridaFlorida
30RegionFloridaFloridaFloridaFloridaFloridaFloridaFloridaFloridaFloridaFlorida
31Formatnananananananananana
32Building TypeColonialGeorgianIndustiralColonialGeorgianIndustiralColonialGeorgianIndustiralIndustiral
33OwnershipOwnedLeasedLeasedLeasedLeasedLeasedLeasedLeasedLeasedOwned
34Building Size5850900090009010.51080010500915010749116467668
35Yard Size0099000002767.500
36Total SQFT5850900099909010.51080010500915013516.5116467668
37Membership Expiration12/31/20283/31/20209/30/20215/31/2023na6/30/202812/31/20239/30/20248/31/20201/14/2021
38Membership End w/ Extensions12/31/20283/31/20259/30/20305/31/2028na6/30/202812/31/20239/30/20298/31/20201/14/2021
39Membership Fees105551005595559055855580557555705565556055
Sheet14
Cell Formulas
RangeFormula
B23:K23B23=IF(SUM(--(B26=#REF!)*(AND(B27:B28<=#REF!,B27:B28>=#REF!))*($B$4:$B$7=B29:B33)*(AND(B34:B39>=$B$8:$B$18,B34:B39<=$C$8:$C$18)))=5,"On","Off")
C39:K39C39=+B39-500
 
Upvote 0
I had to update it for the additional criteria--i didnt' realize that I had no way of ensuring data validation and multiple selections.

The previous one did work, but had trouble modifying for new criteria
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,087
Messages
6,123,050
Members
449,092
Latest member
ikke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top