New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

SUMIF, IF, AND,... which one?

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

SUMIF, IF, AND,... which one?

Postby aniumoh » Thu Dec 08, 2011 12:07 am

Hi

I have 2 tabs of data, Wages and Revenue, and these can be grouped by month and staff member.
I would like to compare wages against revenue per month by staff member on a third tab, how do I create a formula that sums up the tables for me? I don't want to use a pivot table.
I currently use Excel 2003.

Thanks
  • 0

aniumoh
Rookie
 
Posts: 4
Joined: Dec 7, 2011
Reputation: 0

Re: SUMIF, IF, AND,... which one?

Postby Don » Thu Dec 08, 2011 12:20 am

Hi and welcome to the forum!

It looks like you might want to use the sumif function or maybe a basic sum with an IF function since you are in Excel 2003. But, it's difficult to help much beyond that without knowing how the actual data is setup. Can you include a sample workbook?
  • 0

Don
Moderator
 
Posts: 733
Joined: Dec 4, 2011
Reputation: 2
Excel Version: 2010

Re: SUMIF, IF, AND,... which one?

Postby aniumoh » Thu Dec 08, 2011 12:48 am

Hello Don

Thanks for replying.
I've attached a spreadsheet with some data on it.

Thanks
  • 0

You do not have the required permissions to view the files attached to this post.
aniumoh
Rookie
 
Posts: 4
Joined: Dec 7, 2011
Reputation: 0

Re: SUMIF, IF, AND,... which one?

Postby Sisyphus » Thu Dec 08, 2011 4:00 am

Hi,
Do I understand correctly that you wish to extract all CC's income from the Income table and write it to the Income column under the CC header? Or is it that you need only the income for the period mentioned in the Cs. Table?

Then, same for Revenue.

Then, compare the two values?

Your worksheet would be clearer if you would populate it with a few data you extract / calculate manually and add an explanation on the side what you extracted / calculated.

A ratio, expressed as 3:5 is hard to do. Can you live with a percentage, instead?
:D
Regards,
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: SUMIF, IF, AND,... which one?

Postby aniumoh » Thu Dec 08, 2011 8:13 am

Hi
I would like to pick up CC's figures for each month from the Income and Wages tabs.
On the first tab I have copied and pasted the subtotals calculated based on each staff member each month by using filters on the Wages and Income tabs. This is what I would like a formula for.
Income will need to be picked up as a positive figure.
As for the ratio, I don't mind what form the fraction takes so I'm also ok with %ages.

Thank you
  • 0

You do not have the required permissions to view the files attached to this post.
aniumoh
Rookie
 
Posts: 4
Joined: Dec 7, 2011
Reputation: 0

Re: SUMIF, IF, AND,... which one?

Postby Sisyphus » Thu Dec 08, 2011 9:50 am

This formula produces the result in your cell C3:
Code: Select all
=-SUM(IF((Income!$A$2:$A$663=$A3)*(Income!$B$2:$B$663=$C$1),Income!$C$2:$C$663,0))

There is a trick to it, though: You must enter it pressing Shift+Control+Enter at the same time. In this way it will be loaded as a FormulaArray (as opposed to a normal formula) and it will do the job you want (as opposed to a normal formula). :D
Regards,
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: SUMIF, IF, AND,... which one?

Postby aniumoh » Thu Dec 08, 2011 5:07 pm

Thank you very much,it has worked a treat!
Looking at the formula, I don't think I would have gone past the brackets. But I think I can see and follow the logic.
I hadn't heard of FormulaArray before until now. I daresay I will have a few questions to ask once I finish my little research.
Kind regards
  • 0

aniumoh
Rookie
 
Posts: 4
Joined: Dec 7, 2011
Reputation: 0


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 53 guests

cron