Help- New to this - Trying to create formula criteria for the sum of two columns

jasminsaab

New Member
Joined
Jan 24, 2022
Messages
7
Office Version
  1. 365
  2. 2019
Platform
  1. MacOS
So for example,

I want a number inputted into the blue highlighted cell that identifies anytime Column G + Column H equals 7

Screen Shot 2022-01-25 at 11.24.06 am.png
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hi,

I'm not understanding your question, your picture don't help much, is Column G where you have your 1, 4 and Column H where you have your 6, 1 ?
So SUM across by a single Row, then what do you want as a result in the Blue cell? and what about the other cells with Header "Orders"?
 
Upvote 0
Hi,



Hi,

I'm not understanding your question, your picture don't help much, is Column G where you have your 1, 4 and Column H where you have your 6, 1 ?
So SUM across by a single Row, then what do you want as a result in the Blue cell? and what about the other cells with Header "Orders"?
 
Upvote 0
Hi,

I'm not understanding your question, your picture don't help much, is Column G where you have your 1, 4 and Column H where you have your 6, 1 ?
So SUM across by a single Row, then what do you want as a result in the Blue cell? and what about the other cells with Header "Orders"
Book1
ABCDEFGH
1AdultsKids
2House 134
3House 234
4House 344
5House 442
6House 540
7House 623
8House 722
9House 822
10House 913
11House 1050
12House 1132
13House 12110
14House 1322
15House 1451
16House 1513
17House 1611
18House 1715
19House 1820
20
21
22
23Number of OrdersPeople
248 people
257 people
266 people
275 people
284 people
293 people
302 people
311 person
Sheet1
 
Upvote 0
Book1
ABCDEFGH
1AdultsKids
2House 134
3House 234
4House 344
5House 442
6House 540
7House 623
8House 722
9House 822
10House 913
11House 1050
12House 1132
13House 12110
14House 1322
15House 1451
16House 1513
17House 1611
18House 1715
19House 1820
20
21
22
23Number of OrdersPeople
248 people
257 people
266 people
275 people
284 people
293 people
302 people
311 person
Sheet1
I apologise I am bad at explaining this. But under the "number of orders" I would like each box to have a formula that identifies when the numbers in column g (adults) + numbers in column h (kids) equals the required number = 7 (so x amount of families with 7 people)
 
Upvote 0
Thanks for trying to explain and posting with XL2BB, I'm still unsure exactly what you want, is it something along the lines like below?

Book3.xlsx
ABCDEFGH
1AdultsKids
2House 134
3House 234
4House 344
5House 442
6House 540
7House 623
8House 722
9House 822
10House 913
11House 1050
12House 1132
13House 12110
14House 1322
15House 1451
16House 1513
17House 1611
18House 1715
19House 1820
20
21
22
23# of PeopleNumber of OrdersTotal People
24818
257214
266318
275315
284624
29300
30224
31100
Sheet961
Cell Formulas
RangeFormula
E24:E31E24=SUMPRODUCT(--((G$2:G$19)+(H$2:H$19)=D24))
F24:F31F24=D24*E24
 
Upvote 0
Solution
Thanks for trying to explain and posting with XL2BB, I'm still unsure exactly what you want, is it something along the lines like below?

Book3.xlsx
ABCDEFGH
1AdultsKids
2House 134
3House 234
4House 344
5House 442
6House 540
7House 623
8House 722
9House 822
10House 913
11House 1050
12House 1132
13House 12110
14House 1322
15House 1451
16House 1513
17House 1611
18House 1715
19House 1820
20
21
22
23# of PeopleNumber of OrdersTotal People
24818
257214
266318
275315
284624
29300
30224
31100
Sheet961
Cell Formulas
RangeFormula
E24:E31E24=SUMPRODUCT(--((G$2:G$19)+(H$2:H$19)=D24))
F24:F31F24=D24*E24
Yes! That is exactly what I am trying to do hahaha. Thank you! Now I try to insert that formula youve used into E24 on my sheets and it says 0 instead of the right number. Do I need to change something?
=SUMPRODUCT(--((G$2:G$19)+(H$2:H$19)=D24))
that is what I inserted
 
Upvote 0
Yes! That is exactly what I am trying to do hahaha. Thank you! Now I try to insert that formula youve used into E24 on my sheets and it says 0 instead of the right number. Do I need to change something?
=SUMPRODUCT(--((G$2:G$19)+(H$2:H$19)=D24))
that is what I inserted

Change D23:D31 like I show in my sample in Post #6
 
Upvote 0
You're welcome, thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

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