Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Excel IF and SUM.IF

Hello,

I have a guestion about a formula using IF and SUM.IF

In column A1:A22 I have some data (car, bike, bus etc.). In column B1:B22 I have some other data (free, pay, credit card etc.). In column C1:C22 there are some numbers (30, 50, 70 etc.). I'm looking for a formula which looks e.g. for 'car' in column A in combination with 'pay' in column B, for all these combinations in column A en B (so 'car' AND 'pay') --> sum up the numbers in column C of this combination (in e.g. D1).

I have been trying to accomplish this with the IF and SUM.IF function but so far I have not got the result I want. Can anyone help me with a formula for my problem?
Thanks in advance.

F.Hendricks


Post your answer or comment

comments powered by Disqus
I feel certain that what I am looking for is an IF / AND / SUM equation to apply a condition across a range, but I am having a brain block against it and it is driving me nuts.

I pull a monthly report for management from the finance database monthly to calculate how much we spent on certain expenses (consulting, marketing, insurance, etc) by month by department, by general ledger code.

So...here's where I am at so far:

1: I am using using Start/End to collect Data from 6 of our Entities which rollup on the Total spreadsheet. This works fine. No troubles.

2: For each entity, I pull a ledger transaction list with Date, GL, Amount and a few other details.

3: at the top of each workbook I have a matrix lising the GL (vert) and Month (horiz), and the sum of each cell in the matrix should equal the sum of the expenses by GL by Month.

Example: this is what i have below. I have formatted the cell to calculate only based on GL, but have no idea on how to get excel to factor in the date. (cant make it look right below, but just imagine columns)

.................................................. Jan..........Feb..........Mar..........Apr
GL .....Account Name
3210.....Accounting Costs.......... 9,000.00
3240.....Legal Consulting
3250.....Consulting Services
3410.....Advertising
3470.....Marketing
2710.....Development Costs
Total cost .............................. 9,000.00 - - -

DATE.............GL............AMOUNT
1/1/2009..........3210.........5000.00
1/10/2009.........3210.........2000.00
1/15/2009.........3210.........1000.00
1/16/2009.........3240...........100.00
1/18/2009.........3240...........250.00
1/18/2009.........3240............25.00
1/18/2009.........3240............80.00
1/19/2009.........3240...........700.00
2/25/2009.........3210..........1000.00

Any ideas?

In our payroll we put hourly and salary in the same column. I am trying to put in overtime. The formula I have in cell c2 is Code:
I would like to be able to say if a2>=100 then c2=a2

RateTotal HoursAmmt12404801045450104646012344081548720500482400010004040000

Thanks in advance Brett

I am trying to make a spreadsheet for a drug care plan. The formula I am trying to come up with is, if the sum of B2 is less than 250......multiply that sum by 100%. If the sum is greater than 250.......multiply by 25%. I am sorry for a basic question but I would appreciate any help.

How do I say

If the sum of A1:E1 is greater than 1, enter a 1 in the column, but if not
then enter a 0?

I looked at the postings for Sum if and Sum product, but can't get it to do
what I need. I want to count the total hours for Pam if she is listed for a
shift (G9:M16). The hours of the shift are in F9:F16 (see below)

I've tried =SUMPRODUCT((G9:M16="Pam"),F9:F16) & received a value error.
=SUMIF(G9:M17,"Pam",F9:F16) only results in 7.5, counting her first occurence.
F SHIFTS
7:00 AM 2:30 PM 7.5 Pam Alpha
7:15 AM 2:45 PM 7.5 Pam Gloria
8:00 AM 4:00 PM 8 Pam

Hi

I am trying to do a lookup and sum across two spreadsheets, and my syntax just isn't up to it....

I have 2 spreadsheets, thus:

SS1:
Person Product

SS2:
Product Price

In SS1, Person sells many products. What I need is a total amount of sales they've made - i.e. I want to sum all the prices of the products they've sold. Unfortunately, I want to do this from spreadsheet 3 (just a list of names, corresponding to SS1's Person column), hence the lookup on SS1.

Any ideas - I don't really want VB or Access involved, and I'm sure some combination of lookup, countif and sum can help - I'm just not clever enough to work it out!

TIA

Shaun

Hello,
I have 87000 rows of data in 5 columns in Access. I have 750 rows of data in 5 columns in MS Excel. This is all postal codes data.
I want to take the data on the Excel workbook and compare it with the postal codes in Access. Based on that I want to come up with a top 5 most popular postal code and based on the same I want to sum up with the associated columns of data.

Access MDB
i. Postal Code
ii. First three digits of Postal Code
iii. City Name
iv. Neighbourhood Number
v. Popular City Name

Excel Workbook
i. Data (to be aggregated)
ii. Data (to be aggregated)
iii. Data (to be aggregated)
iv. Data (to be aggregated)
v. Postal Code

Is there a way to do it in Excel with VBScripting? I do not how to go around this with the limit of 65536 in Excel. Should I import this into Access and use it there? I have no idea how to use VBScript on this as well. If anybody out there can help me with this, that would be great.
Regards

Please Help!!!
I am trying to have excel find the highest x number in a group of
numbers and sum them up.
Example:

Week 3
156
222
172
185
158
178
166
185
133

I need the Top 3 numbers summed up. (222,185,185) = 592

Changes Weekly:

Week 4
156
222
172
185
158
178
166
185
133
195
178
222

I need the top 4 numbers summed up. (222,222,195,185) = 824

This will go on for 44 weeks.

I need excel to pick the highest 3 numbers and sum them up. I cannot
use the large function because it will pick only 1 number and I need
the x number cannot change on it's own. I need it to pick 222,185,185
and sum them up I have a database with over 150+ columns which I will
have to sort using a macro and then apply the following formula. This
range will increase weekly and for every 3 numbers added, I will need
to pick 1 more of the highest numbers and sum them up. I thought I had
it by finding the x largest number example 4th(using x large formula)
highest number (185) and making a formula with
=SUM(OFFSET(C9,MATCH(C143,C9:C140,0),0,COUNTIF(C9: C140,">"&C143)))
though, I found that if there is more than one number of the same
number, it will error. if i change the > to >=, it will pick too many
numbers. (C143 has the formula to find the x highest number(determined
in another cell).
I hope you understand what I am trying to do. It is very complicated
to me and I am now officially frustrated. Over 10 hours on this
(writing macros for sorting, etc.) Please help if you can. Thank You!

Hello,
I have 87000 rows of data in 5 columns in Access. I have 750 rows of data in 5 columns in MS Excel. This is all postal codes data.
I want to take the data on the Excel workbook and compare it with the postal codes in Access. Based on that I want to come up with a top 5 most popular postal code and based on the same I want to sum up with the associated columns of data.

Access MDB
i. Postal Code
ii. First three digits of Postal Code
iii. City Name
iv. Neighbourhood Number
v. Popular City Name

Excel Workbook
i. Data (to be aggregated)
ii. Data (to be aggregated)
iii. Data (to be aggregated)
iv. Data (to be aggregated)
v. Postal Code

Is there a way to do it in Excel with VBScripting? I do not how to go around this with the limit of 65536 in Excel. Should I import this into Access and use it there? I have no idea how to use VBScript on this as well. If anybody out there can help me with this, that would be great.
Regards

Hi Guys,

New to the forum, and must commend the great help its been over the years, but ahve a question which i cannot find teh answer to, regarding an IF + Vlookup + Sum Formula.

Here is the sheet i am working with,

Cost Table Day 1 to 3 Day 4 to 8 Day 8 or Greater
Adam $10 $20 $30
Ben $15 $30 $45
Chris $8 $17 $28
Dave $14 $29 $32
Ed $25 $28 $35
Fay $7 $17 $24
Gary $11 $21 $31
Harry $13 $22 $36

Days Worked Cost
Adam 2 ?
Harry 5 ?
Fay 9 ?
Ben 15 ?
Adam 22 ?
Gary 7 ?
Dave 4 ?
Chris 8 ?
Ed 3 ?

I need a forumla, that will be able to calculate the cost related to each person, based on the specific cost per day from the Cost Table.

So for example, if we take the second item, Harry.
The forumla, should look up the name Harry in the Cost table, then detect that the days worked for him is 5, then perform a calculation for his cost. ie. 3 days x $13 and 2 days x $22.

Have attached the excel file for better viewing.

Hope that is clear enough to assist me.

Thanks in advance.

Please Help!!!
I am trying to have excel find the highest x number in a group of
numbers and sum them up.
Example:

Week 3
156
222
172
185
158
178
166
185
133

I need the Top 3 numbers summed up. (222,185,185) = 592

Changes Weekly:

Week 4
156
222
172
185
158
178
166
185
133
195
178
222

I need the top 4 numbers summed up. (222,222,195,185) = 824

This will go on for 44 weeks.

I need excel to pick the highest 3 numbers and sum them up. I cannot
use the large function because it will pick only 1 number and I need
the x number cannot change on it's own. I need it to pick 222,185,185
and sum them up I have a database with over 150+ columns which I will
have to sort using a macro and then apply the following formula. This
range will increase weekly and for every 3 numbers added, I will need
to pick 1 more of the highest numbers and sum them up. I thought I had
it by finding the x largest number example 4th(using x large formula)
highest number (185) and making a formula with
=SUM(OFFSET(C9,MATCH(C143,C9:C140,0),0,COUNTIF(C9:C140,">"&C143)))
though, I found that if there is more than one number of the same
number, it will error. if i change the > to >=, it will pick too many
numbers. (C143 has the formula to find the x highest number(determined
in another cell).
I hope you understand what I am trying to do. It is very complicated
to me and I am now officially frustrated. Over 10 hours on this
(writing macros for sorting, etc.) Please help if you can. Thank You!

Hello,
I need help with a formula:
I have two worksheets in an excel file:

Worksheet1 (month):
A -------- B
DATE --- BUDGETED HOURS
01/09 ---- “formula”
02/09
03/09
01/10
02/10

Worksheet2 (week):
A ------- E
DATE --- BUDGETED HOURS
01/03/09 --- 22
01/07/09 --- 19
01/28/09 --- 3
02/06/09 --- 5
03/05/09 --- 11
03/22/09 --- 23
01/07/10 --- 13
01/19/10 --- 0
02/08/10 --- 3

I need a formula for worksheet1, column B, that sums all the hours of Jan 09 (see worksheet2, column E), so I have the monthly hours (worksheet1) calculated from the weekly hours (worksheet 2).
I have to do the same for Feb 09, March 09, Jan 10 and Feb10, but I think if I have the formula for Jan09, I can modulate it myself.

I hope you can help me,
Many thanks
Jody

Hi,

I have three companies that I am collecting data from.

Column A : Company ( A1=A, A2=B, A3=C )

Column B : Days (B1=8 , B2=8, B3=8)

Column C : There is an if statement that sums up days overtime (C1, C2, C3) depending which company is chosen in column A

I want to be able to put in Column D, E, and F, respectively, the sums of the column C1, C2, C3,.... into those cells..

For example.. If company A has a value summed up of 50 days in Column C, I want this transfered into D1

If you totaled all the days in column C that contained company B, I want this transfered into E1.

Another catch is if possible, I would like to be able to when summing these days into D,E, and F,.. I would like to be able to sum these into their corresponding month (Days for Jan for each company, Days for Feb for each company ... )

I hope I havent confused everyone haha..

In overall : Id like to sum the days for each individual company, and then if possible make these sums specific to a certain month.

Thanks!!

Something like this:

=SUM(IF(AND(A:A="A",G:G<=DATE(2011,1,31)),C:C))

"A" being company A
However, this statement in excel sums all the days in column C : when I want it just specific to Company A.

Hi there,

This is my first post in this forum, and I'm afraid I think it is a difficult one. I would be really grateful for anyone's help on this. Please note that I am a complete novice with Excel and would be totally grateful for babyfed answers!

Let me explain briefly the background to my question and what I am trying to achieve.

BACKGROUND

1) I have an excel spreadsheet, which runs to about 12 pages, containing a lot of data. (It is actually within table format in Word, but I will need to transfer this all to Excel).

2) There are 85 rows within the entire spreadsheet.

3) There are only 4 columns used in total, these columns are called 1, 2, 3, and 4. The data cells, under each column heading, contain different combinations of the words Big, Medium, Small, Micro. (Eg, Big/Big/Small/Small or Medium/Medium/Medium/Medium. You get the idea, but there are also other words/symbols contained within each cell.) .

4) Within each row, only 4 cells are used, which contain the one of the words Big/Small/Medium/Micro (along with other data). Each of the 4 cells are under the column headings 1, 2, 3 and 4.

5) Each of the 85 rows (and thus the 4 cells within each row ) within the spreadsheet contain a mix of data – words and numbers. So, row one could contain the data words "Big, Big, Medium ,Small , " , row two could be, "Medium,Medium,Small,Big," row three could be "Micro,Big,Small,Medium" etc.

AIM

If I client says to me, what is the answer to the combination "Big/Micro/Small/Medium", I have to scroll through 12 pages of excel spreadsheet to find the row with that specific combination and this can take ages!

6) I was therefore wondering if it is possible, at the top of the spreadsheet to include buttons or some kind of macro to make finding this information quicker, whereby I can click on a specific combination of buttons (which represent the specific combination requested), which will then output the content of the entire relevant row (all 4 cells) in the "Answer Cell", as illustrated below? (Please note - the output doesn't have to be within one cell - I am open to other suggestions as to how to display the content of the 4 relevant data cells at the top of the spreadsheet).

I set out below how I would like these buttons to look at the top of the spreadsheet. The "xs" are meant to be buttons!

There will only ever be one applicable data row, depending on the specific combination of the 4 buttons I push, and thus the relevant row would need to be displayed where the italic word "answer" is written, ie just within one cell, right at the top of the spreadsheet, as illustrated below. It doesn't matter how wide this answer cell needs to stretch to accommodate the data.

Apologies that the formatting goes off in the below diagram. There are 5 columns listed here. The first 4 columns each contain the buttons headed "Big/Medium/Small/Micro" and the 5th column is meant to contain the answer cell.

1 2 3 4
X Big X Big X Big X Big Answer cell!
X Medium X Medium X Medium X Medium
X Small X Small X Small X Small
X Micro X Micro X Micro X Micro

Thus, if the client says what is the answer to the combination "Small/Small/Small/Small", I could click the 4 buttons which each represent the word "small" (in cells A4/B4/C4/D4, as I have tried to represent in the diagram above), which would automatically locate the specific row within the data section of the excel spreadsheet, and would thus spit the entire contents of that row into the answer cell, at E2.

Is this possible?!

Any help and guidance would be very much appreciated.

FURTHER INFORMATION/CLARITY

I have attached 1 page of the sample data (this is just a copy and paste into excel from word - the data will need to be put in individual cells). It may look complicated, but you will see that each of the 4 cells per row include a combination of the words Big/Medium/Small/Medium (etc) - along with a lot of other data contained within each cell.

This needs to be put in excel.

What I want to do, is to be able to select, using the buttons I discussed, for example, the combination Big/Big/Small/Small, which will extract (for the purposes of this example) the
data in row 2 of the attached document, and reproduce it in another row of my choosing.

Perhaps there is some way of encoding each cell in the background with the word "Big or small or medium or Micro", as appropriate, which would make it easier for excel to find.

Perhaps using some IF equation for the VBA buttons? I.e if the value in column 1 is "Big" (ignore all other values in column 1) AND if the value in column 2 is "Small" ignore all other values in column 2 AND if the value in column 3 is "Medium" ignore all other values, etc, until you have inputted the specific combination, using the buttons, which thus locates the relevant row in the data area, and duplicates this row in another row of my choosing (ie, just below the buttons!).

Hi all!

Stupid title - my apologies to the Powers that Be - I don't understand my problem and hence cannot add a title to it!

Scenario:

1. An amount of money is invested on the first day of any given month of the year;
2. This investment earns interest (calculated at a nominal , not effective, rate) which is credited each month in arrears;
3. Once the interest is credited, it is withdrawn and the effective original investment amount is carried forward to earn interest again (which is yet again withdrawn) until the investment matures in, say, 24 months, when the capital is paid out;
4. On each withdrawal, a certain portion is (with this specific investment, in case you wondered!) susceptible to certain Taxes, which I want to calculate for each Tax year (or portion thereof)

In South Africa, the standard Income Tax year runs from 1 March through 28(9) February of the next year.

I am trying to figure out a formula that will calculate the Taxable Portion of the withdrawal for each Tax year (actually, it may even be done for each month and simply totalled in February for the previous Tax year / portion thereof) until the investment matures.

If an investment is made on 1 June, I need to sum the payments from 06 to end 02 (Tax year1), then sum the totals for months 03 to 02 of the next Tax year and then sum the totals for months 03 to 05 (i.e. 31/05 - ending the 24 month investment period) for the third Tax year.

What can I do do allow for this ever changing differing in the inception month of an investment?

I've attached a s/sheet where I've used all sorts of statements to mark the inception month (year1) as "Mark1", month 2 as "Mark2", month 3 as "Mark3" and the last month as "Mark4", I've used "running months" (1-24 in the example above) etc, but cannot figure out how to use any function I know to help!

Vlookup, IF, Sum, AND etc - None seem to help!

Hope some of you can, though!

Best wishes

Harry

Excel Developer Tip: Summing and Counting Using Mutliple Criteria

This is a straightforward use of the SUMIF function (it uses a single criterion): =SUMIF(A2:A10,"Jan",C2:C10) Count of Sales, where Month="Jan"

Daily Dose of Excel » Counting and Summing Functions

Recently I got a workbook with this formula: =F57+F52+F43+F35+F25+F16+F10+F8+F59+F61+F60. I’m sure you’ve seen a few formulas like this. I know I have.

Is it possible to use a "Sum If" and "And" together?

Hi there,

I'm running a complex if/and on a number of cells on top of a large (150k) data set, and I'd like to optimize it in Excel 2007. It takes a long time to refresh data, and I'm wondering if by some optimization my workbook will actually be usable.

This is what I'm currently using:
=SUM(IF($A4='Raw Data'!$A$2:$A$150000, IF(B$2='Raw Data'!$N$2:$N$150000,1,0),0))

I am trying to accomplish something like:
=SUM(IF(AND($A4='Raw Data'!A2:A150000, B2='Raw Data'!N2:N150000),1,0)))

But I can't get the AND to work in an array; it seems as if it isn't processing all the values in the array independantly. I want to add a value (1) to the sum if the value of A4 matches the data in column A and the value in B2 matches the data in column N.

Any ideas?

I have the following formula in a cell:

=IF(AND(R566=TRUE,R576=TRUE),I15+I20,IF(R566=TRUE,I15,IF(R576=TRUE,I20,0)))

What I am tryring to tell Excell to do is:

If both R566 and R576 = True then sum I15 and I20
If only R566 = True then only return I15
If only R576 = True then only return I20
If All are False return 0

Right now R566 = True and R576 = False, but I'm getting "0" when I (think) I should get the value in I15

Thanks in advance
Harry

=SUM(IF(AND(F2:F4030=Grocery,F2:F4030>=K46),H2:H4030,0))

Trying to sum values that are in the named range Grocery and are >=K46, which is a date.

also tried:
=SUMPRODUCT(--(F2:F4030=Grocery),--(F2:F4030>=K46),H2:H4030)

got a #N/A

I know in just a straight IF stmt, I'd use "=Grocery" for the named range; but it didn't work in both formulas above.

any thoughts?

thanks.

Just wondering about this -

I have sheets that are monthly ones which, at this moment, only started up
since I began to learn more about Excel, last month. I dragged my March
stuff from the Word doc I have it in to Excel, tidied it a bit and auto
summed the totals at the bottom. All well. April was done as the work was
done and auto sum totals and again all OK. Started into May and thought that
I would prefer Year to Date totals to auto update as I entered new data and
came across Consolidate and started a YTD sheet just for those totals. This
works well enough as I specified a particular cell for each of the totals I
am interested in and moved the totals of each sheet, to the same cell on
each monthly sheet and then consolidated the 3 sheets. So, at the moment my
YTD sheet actually DOES do what I want but the formula to consolidate
mentions ONLY those months March to May 2007 and nothing else. When I start
June I have to go back and add, into the consolidate formula, the June total
and the same for every other month of the year.

What I would like to do - as I am likely to forget to update consolidate -
is to set a formula in YTD sheet that would see, say "July 2007" sheet total
and all other months as I create them without me having to go back and
update the consolidate formula. So, if I decided to drag across Feb 2007
from Word to Excel the totals for that month would auto update the YTD
sheet. I actually WILL be going back in time and getting the data across
soon and this would help if that were possible.

So, does anyone know if it can be done? I believe the command to react in
the same way in both Excel 2003 and 2007 but if it makes any difference, I
use the 2007 version. Thanks.

I am trying to create a standings sheet based on scores of games. I have a
new worksheet and I am pulling the scores from another worksheet. What I am
trying to do is for a specific team is to find them in a list of games and
then look at the score and determine if they won or lost. If they one then I
would add one to the wins column. However, I cannot seem to figue it out
without some insanely large formula(which excel tells me is too big). Thanks
for any help.

Here is the code I was using =If(AND('sheet1'!A1='sheet2'!A1,
SUM('sheet1'!B1-'sheet1'!D1)>1),1,0,+If(AND('sheet1'!A2='sheet2'!A 1,
SUM('sheet1'!B2-'sheet1'!D2)>1),1,0,+ etc...

Worksheet 1 (Scores)
A B C D E
H team goals blank goals A Team
1 My team 5 3 Your Team
2 His Team 3 1 Her Team
3 Our Team 1 0 My Team

30 rows

Worksheet 2 (results)
A B C D E
F G
Team Wins Losses Ties Goals For Goals
Against Diff.
1 My Team
2 Your Team
3 His Team
4 Her Team
5 Our Team

I have a spreadsheet that tracks sales of items with a dropdown list to choose my salespeople. I have column"M" as Salesperson #1 and Column "N" is salesperson #2. Most sales only use column M (salesperson #1) But some do require salesperson #2 and those deals count as half a unit when figuring their total sales.

So how can I make excel count column "M" as 1 only when column "N" is blank, but if column "M" AND column "N" is used count each as 0.5?

I need on a seperate workbook page a list of all of my sales people and a "count if " total so I can track their performance.

I know one way to do this is if it's not a split deal to put the same salesperson in column M and column N and count each as 0.5 but I know you guys can help me come up with a more beatiful solution to my problem.

Thanks.


No luck finding an answer? You could always try Google.