• Welcome to the Off-Topic/Schweb's Lounge

    In addition to the Mac-Forums Community Guidelines, there are a few things you should pay attention to while in The Lounge.

    Lounge Rules
    • If your post belongs in a different forum, please post it there.
    • While this area is for off-topic conversations, that doesn't mean that every conversation will be permitted. The moderators will, at their sole discretion, close or delete any threads which do not serve a beneficial purpose to the community.

    Understand that while The Lounge is here as a place to relax and discuss random topics, that doesn't mean we will allow any topic. Topics which are inflammatory, hurtful, or otherwise clash with our Mac-Forums Community Guidelines will be removed.

Using Excel: Finding Certain Values and Adding them

Joined
Dec 1, 2013
Messages
437
Reaction score
3
Points
18
Hi,

Attached is a fictitious scenario- I want to be able to summate a certain value. For example, the value I am focusing on in this instance is 'wages'.

Shown in the picture is a sales table for 2 weeks only. However, imagine if it was for 52 weeks running down like that on one single excel sheet and I want to find the summation of 'wages' for all 52 weeks. Is there a formula for it? This scenario is different because the cells are not aligned together straight in a row/column.

Sum.png
 
Joined
Apr 26, 2008
Messages
2,963
Reaction score
120
Points
63
Location
Belgium
Your Mac's Specs
iPad Pro 12.9 latest iOS
You can use the SUMIF() function where you add the values in column H when the value of column G equals "Wages"

Cheers ... McBie
 
OP
S
Joined
Dec 1, 2013
Messages
437
Reaction score
3
Points
18
Pardon my amateur excel skills, can illustrate it with a formula...
You were right in guessing the Wages were in H column
 
Last edited:
Joined
Apr 26, 2008
Messages
2,963
Reaction score
120
Points
63
Location
Belgium
Your Mac's Specs
iPad Pro 12.9 latest iOS
Assuming that in your print screen the leftmost column is column A, then to sum all wages, the formula would be .....

=SUMIF(G:G;"Wages";H:H)

This formula will search every cell in column G, compare the value of that cell with the string "Wages" and if there is a match, it will add ( sum ) the corresponding cell value in column H.

Am I making sense ?

Cheers ... McBie
 
OP
S
Joined
Dec 1, 2013
Messages
437
Reaction score
3
Points
18
Correct, the leftmost column is A

However, I even copied and pasted your formula in excel on a cell and there was an error where the second G is

Screen Shot 2014-10-30 at 8.48.32 pm.png
 
Joined
Apr 26, 2008
Messages
2,963
Reaction score
120
Points
63
Location
Belgium
Your Mac's Specs
iPad Pro 12.9 latest iOS
Did you put the = sign in front of the formula, I forgot that in my reply ( changed it now )

Update : I noticed that in your formula, the arguments are separated by a comma, whilst it should be a semicolon.
What version of Excel are you using and on what platform ?

To be 100% sure, you could insert the SUMIF function in a cell ( from the menu ) and select the ranges and criteria manually. That will make sure that the syntax is correct.


Cheers ... McBie
 
Last edited:
OP
S
Joined
Dec 1, 2013
Messages
437
Reaction score
3
Points
18
Did you put the = sign in front of the formula, I forgot that in my reply ( changed it now )

Update : I noticed that in your formula, the arguments are separated by a comma, whilst it should be a semicolon.

What version of Excel are you using and on what platform ?

To be 100% sure, you could insert the SUMIF function in a cell ( from the menu ) and select the ranges and criteria manually. That will make sure that the syntax is correct.



Cheers ... McBie


a) Sorry about the screen print before. I actually did put a semi-colon. I sent a new screen print.

b) Yosemite
Office 2011

c) I sent a screen print for this one. Did not work also. Strange

1.png

2.png
 
Joined
Apr 26, 2008
Messages
2,963
Reaction score
120
Points
63
Location
Belgium
Your Mac's Specs
iPad Pro 12.9 latest iOS
When you insert the function SUMIF and you get the grey box ( item c ) did you manually enter gg as range and hh as sum_range. Or did you select range and sum_range by using the mouse and click on column g ( range ) and h ( sum_range ) ..... and thus selecting the complete column.
To me it looks like you typed gg and hh and not selected it via the mouse.

Cheers ... McBie
 
OP
S
Joined
Dec 1, 2013
Messages
437
Reaction score
3
Points
18
You're a genius!! Thanks!

How do you know this much about Excel? Exposed to it a lot at work?
 
Joined
Apr 26, 2008
Messages
2,963
Reaction score
120
Points
63
Location
Belgium
Your Mac's Specs
iPad Pro 12.9 latest iOS
No worries mate, glad I could help.

Cheers ... McBie

I Hate Spreadsheets.jpg
 

Shop Amazon


Shop for your Apple, Mac, iPhone and other computer products on Amazon.
We are a participant in the Amazon Services LLC Associates Program, an affiliate program designed to provide a means for us to earn fees by linking to Amazon and affiliated sites.
Top