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

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

- SUM using IF/AND Array
- Excel If(and( HELP please
- If and Sum Formula
- IF and SUM
- Sum if or sum product?
- lookup with if and sum!
- Access/Excel data to compare and sum conditionally
- Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up?
- Access/Excel data to compare and sum conditionally
- Excel Formula Help : IF + Vlookup + Sum
- Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up?
- Excel: If and sumproduct ? formula
- IF Statement and summing
- Adding 4 VBA buttons to Excel using IF/AND formula.
- Changing Ranges, Vlookup(), If() And Sum()
- Excel Developer Tip: Summing and Counting Using Mutliple Criteria
- Daily Dose of Excel » Counting and Summing Functions
- Sum IF : And Used together
- If / AND statement with Arrays
- IF(AND( Formula help
- Sum(if(and(named range. . . not working
- Consolidate in Excel 2007 and 2003
- If AND for large range of data
- Interesting "Count If" and "Sum" question

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?

I would like to be able to say if a2>=100 then c2=a2

RateTotal HoursAmmt12404801045450104646012344081548720500482400010004040000

Thanks in advance Brett

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

then enter a 0?

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

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

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

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!

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

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.

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!

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

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.

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!).

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

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"

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.

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?

=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

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.

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.

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

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.