How to add up certain words in Excel

SUMIFSubmitted by Visitor on Wed, 12/05/2012 - 23:53Thanks for the info. Last question, How would I write out a formula that would do what you described, but only add the sales amo

How to add up certain words in Excel

SUMIF

Submitted by Visitor on Wed, 12/05/2012 - 23:53

Thanks for the info. Last question, How would I write out a formula that would do what you described, but only add the sales amounts from days that were <8 and >5?

  • reply..

sumif maybe

Submitted by Visitor on Sun, 07/07/2013 - 01:10

I have a spreadsheet with columns I Actual year to date, j budget year to date and 12 columns M to X with each months budget

Is there a way I can fill column j with the sum of correct rows ie m period 1, m and n period2, m,n,o period 3 etc

I had thought of adding a month no at the top of each column so that if col j row1 had 2 then col j row 5 would add m5+n5 row 6 would add m6+n6 and then increase as i change the month no at the top of row j

Any easy way to do this ???
Thanks
Ricky

  • reply..

sumif function

Submitted by Visitor on Sun, 07/07/2013 - 01:11

i want to use sumif function, where i use specific text as criteria in criteria range .

example: use "13ccl01" as criteria
in criteria range cell

CRITERIA RANGE SUM range
13ccl01f01 345
13ccl01f02 234
13ccl01fo3 234
13ccl02f01 345
13ccl02f02 43
13ccl03f01 14

if it is not possible by sumif, then give me another solution

  • reply..

SUMIF using text criteria and wildcards

Submitted by millionleaves on Sun, 07/07/2013 - 01:13

Hi Sajib

This is actually pretty easy to do using * as a wildcard in your criteria:

=SUMIF(CRITERIA_RANGE,"*12ccl01*",SUM_RANGE)

Here's a picture:

Microsoft Excel, using wildcards in the SUMIF function

You can download the original version of this here.

  • reply..

Sumifs with contains

Submitted by J on Sun, 07/07/2013 - 11:58

=SUMIFS(H$12:H$101,$F$12:$F$101,$F118)

H-range is the column to sum
F-range is the column to check criteria in (and may contain mulitiple values ie: a, b, c, etc)
F118 is the cell reference to the actual single value for criteria (ie: a)

so I want to sum up all value in H-range if F-range contains F118

Can that be done?

  • reply..

Sumifs with contains

Submitted by J on Sun, 07/07/2013 - 11:58

I should clarify that F-Range (in each cell of range) contains multiple values...so the contains on F118 would be to target each cell in F-range.

  • reply..

Sumif( with a cell reference containing text for criteria - See

Submitted by Visitor on Tue, 07/09/2013 - 20:09

I am not sure if this is correct way to ask a question, but my question is can I use a cell reference containing text as the criteria in a sumif( function

ie sumif(range.....,B5,range......)

Where B5 may be "Mary" and where there are multiple entries of Mary and I wish to sum the values next to her name

If not a proper way to ask advise, please accept my appology

  • reply..

Yes, you can do this in Excel

Submitted by millionleaves on Tue, 07/09/2013 - 20:14

Hi

First of all, this is exactly the right place to ask a question. Happy to help!

In answer to your main question, yes you can do what you're asking. By putting your criteria value into a cell, you can then reference that cell when you write your SUMIF formula.

I've created a simple example to illustrate how this might work.

I've included column C in the picture because it includes a copy of the formula I used in cell B2.

Essentially, this formula references cell B1 to find the criteria for the SUMIF. In this case it's using Mary. However, I've designed this spreadsheet so you can type any one of the names from the list and the correct sales figure will be calculated. The name in cell A2 will also adjust to reflect the name in cell B1.

You can download a copy of this spreadsheet here.

One final comment ... you should check out the lesson on PivotTables. A PivotTable would be perfect in this scenario for creating a table showing all sales people and the total sales they have made.

Please let me know if you need any more help.

Regards

David

  • reply..

partial text from a cell as criteria

Submitted by Liz on Thu, 08/08/2013 - 10:31

Hi there,

These last few questions get REALLY close to what I'm trying to do. Using this particular example, let's say you wanted to find the total sales of every salesperson whose name starts with M. Is it possible to refer to a cell that has a full name (say, Mary) to get the "M", then use that "M" as the criterion for summing?

I realize the wildcard * function would work for this, if I just put "M" in a cell and used that; unfortunately I'm using larger identifiers, and lots of them--I'd love to be able to refer to a cell that has part of the RANGE text somehow. The real data would look something like this:

RANGE
100214011001a
1004930311001a
101043832001a
101596432001a
101596432001b

CRITERIA
101596432001

SUM RANGE
115304
42273
48167
329977
83918
99311

Thanks for any help you can offer!
Liz

  • reply..

Try SUMPRODUCT as part of an Array formula

Submitted by millionleaves on Thu, 08/08/2013 - 14:36

Hi Liz

Great question .... here's a potential answer for you, although you may need to modify it to suit your needs.

Using my example above, the following formula will find the total for all sales people whose names begin with M:

=SUMPRODUCT(--(LEFT(A6:A16,1)="M"),B6:B16)

You can enter it into the example spreadsheet referenced in my previous comment above. However, the BIG thing to note is that this is an array formula. The LEFT function does not normally allow you to use a range as the first argument (A6:A16), unless you enter the formula as an array.

To do this, click on the cell that should contain the result, enter the formula, then press CTRL+SHIFT+ENTER. When the formula is saved into the cell, it will appear in the formula bar like this:

{=SUMPRODUCT(--(LEFT(A6:A16,1)="M"),B6:B16)}

The {} indicate that this is an array formula. When you edit the formula the {} will disappear; you'll need to remember to press CTRL+SHIFT+ENTER again once you've finished editing it.

In this example, the formula uses the LEFT function to find the first letter in each row in column A and compare it to "M". If it matches, it will return 1. If it doesn't, it will return 0.

The -- in front of the LEFT function is a trick we need to play on Excel to force it to treat the result from the LEFT function as a number rather than a text value. It does this by turning it into a negative number then back again (that's what the -- does). The only way Excel can do this is convert the text value into a number.

The SUMPRODUCT function then multiplies that result by the value in column B for that row and adds up these results for all rows specified in the range (A6:A10). Obviously if there was no match in column A, then the result for that row will be 0, otherwise it will be the value in column B.

So, from here you could modify the LEFT function to return different values to match different criteria. For example, this version of the function would allow you to use a cell (A3 in this case) instead of hardcoding "M" into the function:

{=SUMPRODUCT(--(LEFT(A6:A16,1)=A3),B6:B16)}

The problem with this example is that it will fail if you enter a value into A3 that is longer than 1 character. You could get around this using the LEN function like this:

{=SUMPRODUCT(--(LEFT(A6:A16,LEN(A3))=A3),B6:B16)}

This version of the formula looks at the length of the text string in A3 and takes that many characters from the cells in A6.

Hopefully this gives you enough to work with - post back once you've tried it out and let me know how you got on.

Regards

David

  • reply..

Thanks for the quick reply,

Submitted by Liz on Thu, 08/08/2013 - 15:50

Thanks for the quick reply, David!

This solution is interesting. I like it, but I'm having trouble getting it to work with my numbers--they're alphanumeric, not sure if that's the problem? There's an error that reads, "The formula contains unrecognized text."

That said, I did find a solution: shifting my search range over to a column in which I had removed the final character from each row of the original search range, using left(cell, len(cell)-1). This way, if I have two IDs that are identical but for a final -a or -b at the end, the new column just shows the same ID twice; and then, the SUMIF function finds both and sums them. Less elegant, but it works.

-liz

  • reply..

Hi Liz

Submitted by millionleaves on Thu, 08/08/2013 - 17:46

Hi Liz

Aha - yes, the LEN function assumes it's looking at a text value, not a number.

Try this formula instead, making sure to press CTRL+SHIFT+ENTER to make it an array formula:

=SUMPRODUCT(--(LEFT(TEXT(A5:A15,"#"),LEN(TEXT(A18,"#")))=TEXT(A18,"#")),B5:B15)

This version uses the TEXT function to convert the numbers in column A to an unformatted string, which means that LEN is able to function correctly.

David

  • reply..

yes!

Submitted by Liz on Fri, 08/09/2013 - 06:23

That does it! Thanks, that's great to know. So helpful.

Best,
liz

  • reply..

using this example, but looking for a different answer

Submitted by Kelly on Sat, 03/29/2014 - 18:58

So using the example above I've gotten very close to where I need to be except I am looking for multiple criteria. So what If I want to add Mary AND Olga? How do I put the second criteria in? I've tried sumifs but cant seem to get it right. Using the example below I'd like to know how many entire galleries I've sold so I would need criteria b2, b4 and b5.

A B
1 6 activity clock with home collection
2 1 activity clock with entire gallery
3 1 routine teacher with school collection
4 3 routine teacher with entire gallery
5 7 Entire gallery
6 4 Routine teacher with home collection
7 3 home collections
etc

The other way to do it might be to make the criteria a "text". Like any cell that contains "entire gallery". Whichever one is easier to get my spreadsheet to say I sold Entire galleries. Any help you can provide would be great. I have already gotten so much further than I would have because of your guidance.

Kelly

  • reply..

how to use sumifs

Submitted by Leila Smith on Mon, 06/09/2014 - 18:55

sumifs you have dates with multiple group related to a total hours and I want a particular group sum by a particular date.
sumifs (on 6-5-13) = Leila
how can I write a formula for that

  • reply..

compare text value

Submitted by Antonio on Sun, 03/01/2015 - 03:47

Hi, how should I do next: One person is doing different roles i.e. A,B,C,D... when he is working as "A" I need to sum that as 1 so if A,A,B,C,A,C,B value should be 3 ( 3 times as "A")?
Thank you in advance for help.

  • reply..

SUMIF FORMULA

Submitted by MD PERVEZ KHAN on Mon, 08/10/2015 - 04:43

Hey I have updated my sheet with the same data as mentioned above and also filled the B1 celll with the formula you have showed, but it is showing the value as 0 (zero). It is not working for me. Is there any setting to be done or what may be the reason, please let me know

  • reply..

SUMIF on the condition of text in another cell

Submitted by EldoRado on Sat, 01/02/2016 - 13:16

Hi, I don't think this is answering the question that was asked. I believe I have the same question, if I am not mistaken.

Lets say the spreadsheet is evaluating sales per employee throughout the day:

A B
1 $45 Mary
2 $60 Ronald
3 $100 Terrance
4 $78 Mary
5 $5 Mary
6 $51 Terrance
7 $105 Ronald
8
9 Total of
Mary's
Sales
10
11
n

What formula should be written in A9 to add together only the values in A1 to A7 where the text in Column B says "Mary"?

In other words, how do we make a formula that adds together only Mary's sales: $45, $78, $5

If that is not what the original poster was asking, I would still like to know. Thank you!

  • reply..

Correction to the example

Submitted by EldoRado on Sat, 01/02/2016 - 13:20

Sorry, the letter A in the pretend spreadsheet I wrote above should be sitting above the $dollar amounts column, and the B should be sitting above the employee names column. Oops

  • reply..

When I use this formula I get

Submitted by SeanPatrick on Wed, 03/23/2016 - 12:30

When I use this formula I get an error saying argument must be range. Any idea what would be causing this

  • reply..

This is perfect... but...

Submitted by Toby Keen on Tue, 10/17/2017 - 15:31

This Sumif worked perfectly for me, but is it possible to add one more caveat: for the sum to only work within today to 7 days ago? Using the same table as above would give me enough to figure out on my own sheet.

Thank you anyone who can help!

  • reply..

sumif not matching text cells when it leads with a 0

Submitted by mike on Tue, 08/13/2013 - 09:45

I have column A and column J as text columns, and any cell that starts with a ZERO, somehow does a partial match. for instance, this 000101 matches 00101. When using =SUMIF($A$11:$A$500000,J11,$E$11:$E$500000)

J11 is the 000101, yet it is summing up cells in A:A that are only 00101.

  • reply..

SUMIF Functions with Text

Submitted by Michael on Thu, 08/15/2013 - 11:44

I'm building a CRM in Excel and want to keep track of where my company prospects are coming from (Walk In, Cold Calls, etc.). I have a column named source and each row represents a client. I'm trying to create a chart that will show which source is proving to be the most effective.

For the chart, I know I have to create a separate set of cells with a numerical total. What I want is for that total to be automatically calculated by using, what I suppose, is a SUMIF function. However, I cannot figure it out. In other words, for my "Walk In" total, I'd like that numerical cell to be updated whenever I had a new client to the column that shows the source of the prospect.

To simplify my question: Column 'E' holds all the different sources. Cell H3 will hold the total # of 'Walk In' prospects I have, to be calculated by how many times I have "Walk In' in Column 'E'. If there are 9 cells with 'Walk In' in Column 'E', I'd like for cell H3 to show that by a function.

Thanks for any help/advice you can give!

  • reply..

Disregard!

Submitted by Michael on Thu, 08/15/2013 - 12:10

COUNTIF would be the right function to use. I'm such an idiot. Ha!

  • reply..

Sumif

Submitted by Shelly on Wed, 08/21/2013 - 09:34

Is there a way to use sumif when I want to add only specific cells in a column (not a range)? When I reference the cells in the sumif formula it tells me I have too many arguments? For example =sumif(a2,a3,a5,a6,false,b2,b3,b5,b6). Notice I skip cells a4 and b4. What would I use instead of commas. It thinks I'm making another argument.
Thanks,
Shelly

  • reply..

ref to your question

Submitted by mike on Wed, 08/21/2013 - 09:41

SUMIF uses two columns

1 is the item you are basing your sum on (what you want to match x to x to)
2 what you are summing up if there is a match

  • reply..

Using SumIf to always add to specific sum

Submitted by Dave on Mon, 09/02/2013 - 12:42

Hi there,
I'm hoping this is the right equation to achieve this, but basically what I'm trying to do is have cells automatically readjust to come up with the same sum, if one of the cells is changed. for instance, if i have

4 5 1 10

I should also be able to change the 4 to a 5 and the one will change to 0, so it still equals 10.

Is there a way to do this?

  • reply..

Using SumIF by Referencing To Another Cell as Criteria

Submitted by Cy on Thu, 09/05/2013 - 06:47

I know this is really simple, but I cannot seem to figure this out. I want to add up cells that are greater than an input cell that I can change. My formula reads: =SUMIF(A2:A76, >=N3, B2:B76)

However, this formula gets an error for the criteria, but if I type =SUMIF(A2:A76, ">90", B2:B76) for the criteria it works, but I want to be able to reference a cell as the criteria. So, that I could just change that input cell's value and alter the output of the function.

  • reply..

SUMIF - Use concatenation to reference another cell as criteria

Submitted by millionleaves on Thu, 09/05/2013 - 11:58

Hi Cy

It's simple, yet for some reason it's not.

Excel expects the criteria argument in the formula to be a text string. You can do this using concatenation, which basically means joining text and number values into a text string.

Here's the correct version of your formula:

=SUMIF(A2:A76, ">="&N3, B2:B76)

Regards

David

  • reply..

Thanks David!!

Submitted by Cy on Thu, 09/05/2013 - 13:43

Thanks David!!

  • reply..

formula to add value n if the date is m to cell O

Submitted by Chuck on Thu, 09/05/2013 - 15:01

hello I need help to build the folliwing, I need to add numbers in to one cell depending on a cells value and the specific month, example, I made purchases for a cost center 211 on September 12, for a total of 5, then another for 10 for same Cost center on sep 20, how can I have this to be sum in to one cell for my actual spends, hope to make sense.

  • reply..

Adding up sales by month and cost centre

Submitted by millionleaves on Thu, 09/05/2013 - 15:54

Hi Chuck

One option is to create another column in your spreadsheet next to the date field and use the Month() function to determine the month for the date in the cell next to it, e.g.:

=MONTH(A2)

Note that the MONTH function returns the number of the month, e.g. 9 for September, 10 for October etc. It doesn't distinguish between different years, so the MONTH function would return 9 for both September 2012 and September 2013.

If you have the following columns you could then use the SUMIFS function to calculate what you're after:

  • A2:A10 contains the date
  • B2:B10 contains the month number
  • C2:C10 contains the cost center number
  • D2:D10 contains the purchase value

The function would look like this:

=SUMIFS(D2:D10,B2:B10,9,C2:C10,201)

Note that I've used 9 as the third argument since I want to add up purchases in September. 201 is the cost center that I want to find the sum for.

You can read more about SUMIFS here. (opens in a new window)

The other option is to use a Pivot Table. This would quickly give you a report that showed purchases for each cost centre, broken down by month. You could just easily get a report by month broken down by cost centre. The advantage of a Pivot Table is that you wouldn't need to add a separate column to calculate the month. You can read how to create a pivot table here, and read the lesson on how to group dates in a pivot table by month here.

Finally, this article on another site shows how to use SUM in an array formula to do something similar to what you are trying to do. I've tried applying it to your requirement without success (so far) but if I find a solution I'll let you know.

Regards

David

- See more at: //fiveminutelessons.com/learn-microsoft-excel/using-sumif-add-cells...

  • reply..

Sumif

Submitted by Cory on Mon, 09/16/2013 - 22:11

I have a spreadsheet with columns representing the days of the week. Rows representing employees. In each cell we will enter the daily hours for each contractor we work for. We have two rates we use. I would like a formula that will add up the hours from each row based on the rate we charge. Thanks for any help

  • reply..

Hi Cory

Submitted by millionleaves on Mon, 09/16/2013 - 22:21

Hi Cory

If this was my spreadsheet, I'd have an additional column that has the rate for each employee.

Let's say you have these columns:

  • Column A has the name of the employee
  • Column B has the rate for that employee
  • Columns C-I have the day of the week (i.e. the hours worked by that employee
  • Column J has the sum of columns C-I, which will be the total hours for the week
  • Column K has the amount each employee is to be paid

In column J, I'd have this formula to calculate the hours worked for the employee in row 3:

=SUM(C3:I3)

In column K, I'd have this formula to calculate the amount due to that employee:

=J3*B3

I might also consider combining columns J and K into one with this formula:

=SUM(C3:I3)*B3

Hopefully that helps?

David

  • reply..

sumif

Submitted by cindy on Wed, 09/25/2013 - 06:25

Hi David, not excel savvy at all

refereeing to the following extract out of my spread sheet
StockCode Description AbcClass MtdQtySold 12 mnt AveQtysold 3 mnt AveQtysold QtyOnHand QtyOnOrder Supplier Warehouse MinimumQty REORDER QTY
300651 90MM BASKET STRAINER WASTE 0 3 0 295 0 FRANKE JB 0 -295
300651 90MM BASKET STRAINER WASTE 0 8 3 37 0 FRANKE CT 1 -36

I have created this spreadsheet using ODBC( we are using syspro) but the fields it pulls from is warehouses. I would like this sheet to show the total all values per stock item, not per warehouse. will sum if work here
regards Cindy

  • reply..

Try a Pivot Table instead

Submitted by millionleaves on Wed, 09/25/2013 - 14:36

Hi Cindy

My guess is that you'd be better off using a Pivot Table to do this. I'm assuming your spreadsheet has lots of rows, lots of stock codes, and multiple warehouses. Keeping track of all of those with SUMIF formulas is going to be a nightmare! A Pivot Table will help you make sense of this data much more easily, and will give you a lot of options for slicing, dicing and reporting your data in different ways.

You can check out our lessons on Pivot Tables here (opens in a new tab). They will all be relevant to what you're trying to do. You can use the techniques covered in the Grouping By Date in an Excel Pivot Table lesson to group your data by Warehouse rather than by Date (although Pivot Tables will let you do both at the same time!).

Regards

David

  • reply..

Excel adding a column of text names

Submitted by Tim on Tue, 10/08/2013 - 20:25

I have a workbook, where I have a column A titled "Last Name". I the rows below, starting at A3 proceeding forth. I have the last names. I want to be able to sum up the total number of names in column A. How do I write a formula to accomplish this task? When I write a simple SUM formula, it returns 0, as the text isn't seen as a number. (=SUM(A3:A17)

When I write a SUMIF formula, I get a #VALUE.

=SUMIF(A;A, "Last Name", A3:A17)

I would appreciate any help.

Thanks

  • reply..

Try COUNT or COUNTIF

Submitted by millionleaves on Tue, 10/08/2013 - 20:53

Hi Tim

Sounds like you simply want to add up the number of names in the column. Try one of the COUNT functions:

  • COUNT() will count the number of cells in a range.
  • COUNTA() will count the non-blank cells in a range
  • COUNTBLANK() will count the blank cells in a range.
  • COUNTIF() will count the number of cells in a range that meet the criteria you specify.
  • COUNTIFS() extends COUNTIF() to support multiple criteria.

You can read our COUNTIF lesson here, and I've got the other functions on my "lessons to write list", although they are fairly straightforward.

Regards

David

  • reply..

SUMIFS with Multiple Criteria But Need to Use "OR"

Submitted by MAGGIE on Thu, 10/10/2013 - 11:43

Hello. I am using a SUMIFS formula to bring back actual spend for contracts and purchase orders. Here is my current formula:

=SUMIFS(COGNOS!$AN$2:$AN$10000,COGNOS!$AG$2:$AG$10000,D7,COGNOS!$AH$2:$AH$10000,E7,COGNOS!$AI$2:$AI$10000,F7)

My "sum range" is in one column on my COGNOS tab (COGNOS!$AN$2:$AN$10000) which is a dump of all actual spend from our reporting system. My "criteria_range1" is in another column on my COGNOS tab (COGNOS!$AG$2:$AG$10000) which looks to the contract number in the dump of actual spend. My "critera1" is on my project reporting tab which has a list of the contracts and purchase orders so it looks to cell D7 on that sheet (column D being contract number). My "criteria_range2" is in another column on my COGNOS tab (COGNOS!$AH$2:$AH$10000) which looks to the contract release number in the dump of actual spend. My "criteria2" is on my project reporting tab and looks to cell E7 on that sheet (column E being contract release).

This part of the formula is working fine. I want it to return the sum of actuals based on the contract number AND release number. However, I need to figure out how say OR this PO #. So it is a contract or PO on each line in my project report.

So it will look to "criteria_range3" (COGNOS!$AI$2:$AI$10000) which is PO # in the dump of actuals and then look to see if there is a match on the project report tab which is column F.

How can I tell the formula to look for contract & release OR PO# in the three different columns?

If you need more information, please let me know. Thanks so much for any help!!!!!

  • reply..

A quick question

Submitted by millionleaves on Thu, 10/10/2013 - 11:48

Hi Maggie

My first thought here is to use SUMIFS twice in the formula. The first instance would what it is now - finding the sum for contract & release. The second instance would look for PO#. You'd then add those together to get the total result.

Would that work?

David

  • reply..

Please can u share me sample

Submitted by faheem on Sun, 12/07/2014 - 04:20

Please can u share me sample file for this

  • reply..

Yes that worked. Thanks! I

Submitted by MAGGIE on Thu, 10/10/2013 - 13:42

Yes that worked. Thanks! I don't know why I didn't try that!

  • reply..

Glad I could help!

Submitted by millionleaves on Thu, 10/10/2013 - 13:55

Hi Maggie

That's great - sometimes the simple solutions are the best!

Regards

David

  • reply..

Use of "Or" statements when using Sumifs

Submitted by Rebecca on Wed, 10/23/2013 - 07:18

I have a spreadsheet to track contracts. Within my spreadsheet I am trying to sum the costs of contracts if one of 3 columns is marked yes (the columns indicate specific criteria about the contract holder and I simply put a Y in the column if the attribute is appropriate). The problem is, for any given contract the there could be marks in any one to all three of the columns. The formuals I have been able to figure so far, will add the contract mulitple times if mulitple colums are marked for each contract. Any suggestions?

  • reply..

variation on the example given

Submitted by Brigitte on Tue, 11/19/2013 - 02:05

To easily illustrate what I need I'll give an example. C1:C5 = 5,5,5,5,3 E1:E5 = 1,2,3,1,2 In column F I have the formula: =sumifs(E1:E5;1;C1:C5) This gives me exactly what I need IF the number in Column E=1. I also need it to calculate all the C #'s for E=2, and E=3. I hope that makes sense. Also just to complicate it a tiny bit more... In reality my Column C numbers are times ( 3.12 for example) Minutes and seconds. Is this possible?

  • reply..

SUMIF and COUNTIF

Submitted by Bernadette on Tue, 12/17/2013 - 02:09

My SUMIF and COUNIF function does not pick up the information. I figured out that the cell it is looking at contains 16 characters and it seems as if the last figure is not taken into account and the SUMIF and COUNTIF is only taking the first 15 characters into account.

How can I change the length of the string so that the formula will pick up all 16 characters or is there a limitation to excel?

  • reply..

This doesn't sound like a problem with Excel

Submitted by millionleaves on Sat, 01/04/2014 - 13:37

Hi Bernadette

What you're describing doesn't sound like normal Excel behaviour, so I suspect that the source of the problem lies with the data in your spreadsheet. Are you able to upload a copy of your spreadsheet in a reply to this post and I'll see if I can troubleshoot it for you.

Regards

David

  • reply..

I am needing a different

Submitted by Yvonne on Fri, 01/03/2014 - 18:17

I am needing a different formula for the cells AG-AM. I want to be able to put in 0.5 all the way up to 8 in front of the letters to designate how many hours of each type of leave are being used. For example, if on the 1st and 2nd I used 2 hour of illness, I want to be able to put just 2I in and have them total at the end of the month in the I column to read 4. Is this possible? Right now, it only works if I put I in the columns and then it adds that up, but sometimes I don't have people taking full days off. They may only take a couple hours at a time. Thank you for the help!

  • reply..

Use of SUMIF Function

Submitted by William Wong on Mon, 01/06/2014 - 10:23

Since I haven't used it in a while, I was looking for a refresher on the use of the SUMIF function and came upon yours. Your statement "range is the range of cells you want to add up. It is required for the function to work." is wrong or at least misleading. It is rather "the range of cells that you want to apply the criteria against" (per Microsoft). Also, you're too loose or inconsistent with your terms. In your 2nd example for instance, your Col C is referred to as "sales quantity" (per title), "Sales Amount" (per header for table), and "sales value" (per second bullet point).

  • reply..

Sumif Command

Submitted by Sunil on Tue, 01/07/2014 - 21:33

I could not upload the excel File which I am working on. However if you could help me in solving below problem.
I want to specifically sum the cells among F1,F4,F7,F10 only when corresponding cell among B1,B4 B7,B10 have "Sunil" written in it.

  • reply..

Use SUMIF rather than SUMIFS

Submitted by millionleaves on Wed, 01/08/2014 - 10:45

Hi Sunil

The lesson on this page is about using the SUMIFS function, which allows you to use multiple criteria within your formula. Unfortunately, the scenario you've described doesn't fit with what the SUMIFS function can do. Essentially, the SUMIFS function looks at each of the criteria specified and adds up all the values that match all criteria. You can read more about the SUMIF function here (opens in a new window).

Your example is different because it looks at different ranges and applies one criterion to each. Therefore, I would use the SUMIF function multiple times in one formula, one for each range, as shown in this solution:

=SUMIF(B1,"Sunil",F1)+SUMIF(B4,"Sunil",F4)+SUMIF(B7,"Sunil",F7)+SUMIF(B10,"Sunil",F10)

Note that you could use SUMIFS instead of SUMIF in this example but it wouldn't make the formula shorter or better.

Warning - don't just change SUMIF to SUMIFS in my example - the SUMIFS syntax is different, so that won't work. Here's my solution above converted to use SUMIFS - note how the arguments are in a different sequence:

=SUMIFS(F1,B1,"Sunil")+SUMIFS(F4,B4,"Sunil")+SUMIFS(F7,B7,"Sunil")+SUMIFS(F10,B10,"Sunil")

Note that if you are looking for a formula that looks at all of the cells in the ranges B1:B10 and F1:F10, then this is the answer:

=SUMIF(B1:B10,F1:F10,"Sunil")

I hope that helps.

Regards

David

  • reply..

Thanks David

Submitted by Sunil on Thu, 01/09/2014 - 03:28

Thanks David
Earlier I tried the SUMIFS as you have written. I was trying to make the formula shorter.
I found a solution by adding another range in separate column G where I specified a unique letter "A" for B1,B4,B7 etc and then using SUMIFS.

=SUMIFS(F1:F10,B1:B10,{"SUNIL"},G1:G10,"=A")

Although this was increased a column and I had to enter A at respective cells location but It helped in avoiding to write lengthy formulas.
I guess Excels can't looks at different ranges and applies one criterion to each by using a single formula.
Anyways Thankyou very much.

  • reply..

Difficulty getting the correct answer when using the SUM formula

Submitted by Echo Flood on Thu, 01/30/2014 - 09:41

I have been trying to use excel to match my business income to my business deposits so I can keep up with taxes. I sometimes have negative value income due to commission withdraws on weeks we don't sell all the route inventory. The Checkbook and Income Tally are on two separate spreadsheets. Items I want to sum are in the Checkbook sheet in Columns D and E, but I only want to sum them if their corresponding data in Column G has been labeled as Income. My formula is not coming up with the correct number. Starting in February, I'll need to further separate my sumif by which month they fall in, which will change the formula again, but I can't even get the basic one to work. The formula I think should work is coming up with a total of -1870.00, when it should say 4345.00. I have highlighted the cells I want it to add, but the real spreadsheet would not have any highlighted cells. I have generalized my spreadsheet so it can be made public if it would be a useful tool to someone else.

  • reply..

This is the formula I THINK

Submitted by Echo Flood on Thu, 01/30/2014 - 09:42

This is the formula I THINK should be working, but it isn't. =SUMIF(Checkbook!G:G,"Income",Checkbook!D3:E650)

  • reply..

Further clarification

Submitted by Echo Flood on Thu, 01/30/2014 - 09:44

I should clarify, they're in 2 sheets, within the same workbook and file. Not 2 separate spreadsheet files.

  • reply..

SUMIFS with text (DO NOT want to sum specific text)

Submitted by Nate on Mon, 02/03/2014 - 10:36

Hi,

Is there a simple way to sum cells that DO NOT meet a certain criteria? For example =SUMIFS(Production!I$6:I$108500,Production!$B$6:$B$108500,Summary!$B$4,Production!$D$6:$D$108500,Summary!$B$25)

I would like the change the second criteria from "Summary!$B$4" to everything EXCEPT "Summary!$B$4". Is there a way to do this using the SUMIFS function? The criteria I am using is text that I would like to easily change in the "Summary!$B$4 cell (i.e. "Australia" so I would like to sum everything that does NOT have "Australia").

Thanks!

  • reply..

Is there a quick way to SUMIF a large number of cells

Submitted by Sam on Thu, 02/06/2014 - 11:54

Hi, I hope someone can help. I have a file that is for a Restaurant roster and for about 32 employees. And I am wanting to sum the number of hours worked by each employee on a given day of the week. I have started to use SUMIF but this is taking a long time... Is there a quicker way to use SUMIF or another command in Excel.

My file at the moment has 5 different criteria like if the employee is working Mon-Fri before 6pm, Mon-Fri after 6pm, Sat before 1pm etc.. And using the SUMIF will take me hours to try and count how many hours each employee works... Hope this makes sense..

Ideally what I would like is to highlight a range of cells and say if Bob has worked in any of these cells add the hours... Cheers. Sam

  • reply..

Thank You!

Submitted by Ross on Tue, 02/18/2014 - 07:44

Excellent information, well put - good examples. It would be cool if the developers of this website added an outline sidebar or let you make a custom one to portray the layout of the page.

  • reply..

Excel Sumifs

Submitted by hubloo on Tue, 02/25/2014 - 02:26

Hi, I was wondering if you could help me.I am looking to write a SUMIFS statement to get total of el2 40805, 42302 & 40108. Any suggestion would be very much appreciated. Many thanks.

  • reply..

I'm needing to do formula

Submitted by Julie on Thu, 03/06/2014 - 17:16

I'm needing to do formula adding up how many computers have a certain operating system. So in one worksheet all the computers are listed with what windows is installed. On another worksheet, it will have a running total of each system. How do I write this formula?

  • reply..

Criteria value has too many digits

Submitted by Ronald Myer on Thu, 04/10/2014 - 06:58

I used basic SUMIF, but the values in my criteria has 20 characters. The SUMIF did not look at all the characters, only about the first 15. It there a limit to the number of characters? The attached is a very simple example that shows ABC company should not be included in the SUMIF, but it appears it is because only the last 2 characters are different. As shown in the attached, if I change the first 2 characters, SUMIF works. This would suggest that criteria can only look at a fixed number of characters that is less than 20, which makes no sense.

  • reply..

Hi Ronald

Submitted by millionleaves on Fri, 04/11/2014 - 11:19

Hi Ronald

Thanks for your question. You're right that Excel is only using the first 15 digits in the SUMIF function, which is why your formula isn't returning the results you're looking for.

However, this formula will do the trick for you:

=SUMPRODUCT(--($A3:$A5=A3),C3:C5)

This formula works in the following way:

  • The SUMPRODUCT function is usually used to find the total value of multiplying values in one column with values in another column and summing the individual results. You can read our lesson on SUMPRODUCT here.
  • We use --($A3:$A5=A3) to turn the values in A3:A5 into 1s and 0s, where 1 = "Matches A3" and 0 = "Doesn't Match A3". We don't get to see those 1s and 0s - Excel stores them internally as part of the calculation it does to get the result.
  • Excel then uses those 1 and 0 values in the SUMPRODUCT function.

I hope that helps solve your problem.

Regards

David

  • reply..

SumIF limited to 15 characters

Submitted by Ronald Myer on Wed, 04/30/2014 - 14:13

David, Your solution worked perfectly. Thanks for your expertise.

  • reply..

=SUMIF problem

Submitted by Cynthia Sliker on Thu, 05/22/2014 - 16:23

I would like to create a running chronological total of contributions by date received, and have written the following formula:
=SUMIF('1314'!F:F,"<="&A2,'1314'!E:E) where column F is the date of the contribution, and E is the amount.

It appears to be a valid formula, but when I drag it down to populate the range of cells representing the date range of our fiscal year, it just shows 0's all the way down and does not add the contributions correctly.

Thanks for your help!

  • reply..

Thank you for this

Submitted by Kathryn on Mon, 06/23/2014 - 15:02

It was just what I needed!

  • reply..

Excel Lessons - General comment

Submitted by Laurie Rupert on Mon, 06/23/2014 - 17:58

I have been using excel since the 90's but using your wonderful lessons to extend knowledge of many things I never used... and it's working... thank you... may be able to land the higher paying job as a result of these...

It is most helpful to me to have an example worksheet after your text details providing confirmation that I truly understand.

thanks again,
Learning after 50... didn't think I was capable...
Washington, U.S.

  • reply..

Summing on numeric criteria

Submitted by noman arshed on Fri, 07/25/2014 - 23:32

Hi
Suppose i have following data
number income
3 0
3 10
3 0
2 5
2 5
1 0
3 15
3 5
3 0

I need to sort them in such a way that each group unique not repeated are shown with total income of that group
number income total income
3 0 10
3 10 10
3 0 10
2 7 12
2 5 12
1 0 0
3 15 20
3 5 20
3 0 20

please not that group name 3 is not same as previous one actually this 3 is telling the group size not the group name so should not be added in each other
please let me know how to do it?
Thank

  • reply..

SUMIF

Submitted by PRASAD on Fri, 10/03/2014 - 13:48

How do i calculate total time for a day for a individual using SUMIF. My table will be as shown below:
Date Name Total Min
10/1/2014 Sam 0:50:00
10/1/2014 Jeff 1:40:00
10/1/2014 Mike 0:55:00
10/1/2014 Rick 7:30:00
10/2/2014 Sam 1:05:00
10/2/2014 Jeff 1:20:00
10/2/2014 Mike 1:50:00
10/2/2014 Rick 0:18:00
10/3/2014 Sam 0:40:00
10/3/2014 Jeff 1:03:00
10/3/2014 Mike 1:50:00
10/3/2014 Rick 2:08:00

What i would need is a formula to calculate everyday what is the total time for each individual...Please help

  • reply..

Very Helpful

Submitted by StuartB on Thu, 11/20/2014 - 06:56

Thank you for saving my brain from exploding. I spent hours googling to find out how to add negative numbers that were in a column with plus numbers and get results for both. Found this lesson and it was light bulb time.

  • reply..

SUMIF with text

Submitted by Tamar on Tue, 12/16/2014 - 11:39

Hi,
I'm wondering if you can help me! I am trying to find the sum of cells that meet certain text criteria. I'd like to find the total amount of money spent if the category says food, and the month says Jan, Feb. or March. Is this possible for excel to do? It needs to fit the 2 criteria of FOOD and the specific months. Thanks for any help!

  • reply..

Sumif

Submitted by Stephen on Thu, 01/08/2015 - 09:28

I am attempting to make a database that will help track supplies brought out to patients by our staff.
column a would be supply item quantity
column b would be size
then i would set up a list at the bottom where it would sum up all large in one cell, a sum in a different cell for small, and so on.
I know that this will need to be several formulas or a formula for each cell that would contain a sum.
I need a starting formula for the sumif.
When I go to the sumif set up it asks for range, criteria, and sum range.
Which data would go in each?
Thanks if you can help in any way.

  • reply..

Nevermind I just figured it

Submitted by Stephen on Thu, 01/08/2015 - 09:30

Nevermind I just figured it out.

  • reply..

Sum if

Submitted by Jenita on Mon, 02/02/2015 - 15:55

Thank you so much for these hick lessons, they help me tremendously on my jobs,

  • reply..

not sure if this is the right place for this or not

Submitted by ziggy on Tue, 02/17/2015 - 18:33

Hi. I have attached my sample spreadsheet and pivot chart to try and show you what I need because I am not very good at explaining things.
If Week 2 totals 66 and Week 3 totals 55, then I want to be able to have a formula that will be in my "need help here" column to state that the average for week 2 is 66 and then for week three the average is 60.5.
There will be 52 weeks in total, and no two weeks will have the same data so it is dependent on the week number and hours columns.
Hopefully I have explained this clearly enough. Thanks for any assistance, it is greatly appreciated.

  • reply..

Your lessons are so helpful!

Submitted by Sahar on Wed, 03/04/2015 - 01:49

Your lessons are so helpful!

  • reply..

Question

Submitted by Claudia on Tue, 04/21/2015 - 00:57

Hi, I would like to add all the 1's in a row, but not the sy=um of them, just the number of them.

For example if I have 1 1 1 1 1, I want it to add up to 5 (which I know it would do with sum anyway) but I also want to have 0 0 0 and have it add to 3.

Is this possible in Excel?

  • reply..

Try a COUNT function

Submitted by millionleaves on Tue, 04/21/2015 - 01:17

Hi Claudia

Sounds like you want one of Excel's COUNT functions. There are several to choose from. If all your values are numbers, then the COUNT function is the best one:

=COUNT(A1:A10)

This will count all the cells containing numbers in the range from A1:A10. It will ignore empty cells and cells that contain text.

Regards

David

  • reply..

sumif in excel

Submitted by omar shaqsi on Sun, 04/26/2015 - 11:55

Great explanation and examples!!!

  • reply..

I can't get this to work?

Submitted by Samantha on Mon, 05/11/2015 - 08:58

I'm trying to use the SUMIF as stated above and my spreadsheet is returning an answer of 0 which I know is incorrect. Can anyone please help?
Many thanks

  • reply..

Can you provide an example of your formula?

Submitted by millionleaves on Mon, 05/11/2015 - 11:16

Hi Samantha

Can you provide a copy of your formula so we can troubleshoot it?

Thanks

David

  • reply..

what formula to use as sumif

Submitted by Eli Slabaugh on Wed, 05/13/2015 - 15:07

I'm trying to make up a employee time sheet. What kind of formula do I use? I'm trying to set up total regular hours 40 per week, and let's say the employee works 45hrs I want (G20) to max out at 40hrs and sum of the remainder 5hrs to (H20) what is the best way?
Please help any way posible. Thank you!!

  • reply..

Some thoughts on your timesheet problem

Submitted by millionleaves on Wed, 05/13/2015 - 15:51

Hi Eli

It's hard to give a definitive answer without a bit more information, but it sounds like you need G20 to have a formula which will add up a list of other cells containing time worked, and then return either the sum of those cells or 40 if the sum exceeds 40 hours. H20 would then either be zero (if the total is less than 0) or the number of hours over 40 that were worked.

If the cells containing the time values were A1:A20, then I'd try this in G20:

=IF(SUM(A1:A20)>=40,40,SUM(A1:A20))

This formula will add up the values in A1:A20. If they are equal or greater to 40, then return 40. Otherwise, return the SUM(A1:A20).

In H20, I'd do this:

=IF(SUM(A1:A20)>=40,SUM(A1:A20)-40,"")

This formula will also check if the values in A1:A20 exceed 40. If they do, it will calculate how many hours over 40, and return that. If the total hours is less than 40, the formula will return an empty string ("") which will make H20 appear empty. You could write it like this if you'd prefer to return a 0 value:

=IF(SUM(A1:A20)>=40,SUM(A1:A20)-40,0)

You can learn more about the IF function in this lesson.

I hope that helps. Feel free to ask more questions if this doesn't meet your needs.

Regards

David

  • reply..

How do I edit the value of a product OUTSIDE of the critera - Se

Submitted by Sean on Thu, 05/21/2015 - 11:54

So basically I have made a spreadsheet comprising +£ and -£ figures. I have also added criteria's "W" and "L" that I wish to make 2 separate conversions for EACH outcome.

For every "L" marked in column M, I want to subtract %5 from the number in column N of that same row to display in column P. For every "W" marked in column M (i.e NOT satisfying the critera) I want the number to stay THE SAME as N, but displayed in column P.

I have put together a formula I found in the comments under the SUMIF function, but have converted it to SUMPRODUCT.

Here is the table example:

M--------------------N-----------------P
Win/Lose_____Total_____Commission
W___________£9.80_____£0.00 (needs to be 9.80(N2))
L____________£14.32____£13.60
L____________£5.00_____£4.75
W___________£9.75_____£0.00 (needs to be 9.75(N5))

This is how the formula currently stands:

{=SUMPRODUCT(--(LEFT(M2,1)="L"),N2*(1-5%))}

Now, my main problem here is that I already have a pre-filled spreadsheet with all the correct conversions, and I am looking to continue without having to do it all manually. Currently with this formula, all cells in column P (marked with "W" from column M) are being converted to 0, erasing the previous calculated result of another formula in column N. How do I change the "0" outcomes to display the calculation from column N?

Also, I wish for the formula to continue indefinately. I've tried using M:M but it doesnt seem to work. I'm using AutoFill for now, is there a better way of doing this?

Any help would be great!

Thanks

  • reply..

May be error in some cases

Submitted by Ram Chander on Fri, 05/29/2015 - 23:41

A B
03021642210201400005 100
05102470603201552334 200
00053472705201500558 300
00053472705201500508 400

03021642210201400005 10
05102470603201552334 20
00053472705201500558 30
00053472705201500508 40

=SUMIF(A$1:A$10,"00053472705201500558",B$1:B$10)
Result = 870
but Result is 330

  • reply..

sumif

Submitted by RB on Thu, 06/04/2015 - 13:06

I tried this with the criteria being <0 and it did not work. I am trying to add a column of numbers where the negative number is in red and positive in black. I want to know the total of all the negative numbers. I could try setting the criteria to the color red but don't know how to do that. All the negative numbers are preceded by a minus sign, so I am curious why my formula doesn't work.

  • reply..

let's assume that column in

Submitted by Ram Chander on Wed, 07/01/2015 - 03:44

let's assume that column in which values are is A, so start and do work with this function.

In Column B
=if(A1<0, A1,"")

In Column C
=if(A1>0,A1,"")

  • reply..

SUMIF

Submitted by Alison on Mon, 06/29/2015 - 08:07

I tried this on a column containing 1s and 0s (representing whether an office machine has performed or not). My function is =SUMIF(E3:E165, "=0") but this always returns 0. However, if instead I use =SUMIF(E3:E165, "=1"), that counts the 1s perfectly, so that I can simply subtract that number from the total number of entries. But why won't the "=0" version work?

  • reply..

Please send your file.

Submitted by Ram Chander on Wed, 07/01/2015 - 03:46

Please send your file.

  • reply..

Using SUMIF to add up cells in Excel that meet certain criteria"

Submitted by Alison on Fri, 07/03/2015 - 02:31

Thanks, Ram. Please find my Excel file attached. Look forward to your comments. Alison

  • reply..

File is not attached.

Submitted by Ram Chander on Fri, 07/03/2015 - 03:40

File is not attached.

  • reply..

Using SUMIF to add up cells in Excel that meet certain criteria

Submitted by Alison on Fri, 07/03/2015 - 04:42

Hi Ram,
Sorry. I can send you a screenshot that shows the file WAS uploaded. I have uploaded it again here. Not sure what to do if you still can't see it. I can send it to you by email if you give me an email address.

  • reply..

Files are private to administrators

Submitted by millionleaves on Fri, 07/03/2015 - 14:14

Hi Alison, Ram

Unfortunately, security is tight on who can see uploaded files. Currently, only a member of the Five Minute Lessons team can see uploaded files.

Ram - thanks for trying to help - much appreciated. For the record, here's a screenshot of the spreadsheet data:

Excel SUMIF function sample data

Alison - the SUMIF function is working as designed - you've asked it to add up all the cells that contain zero. Logically, the sum of 9 zeros, as shown in your example, is zero.

here are a couple of potential solutions.

  1. Use COUNTIF instead of SUMIF in your formula, i.e. =COUNTIF(E3:E32,"0") - note there is no need to include the = sign.
  2. Use COUNTIF on your Success/Failure column, i.e. =COUNTIF(D3:D32,"Fail") and =COUNTIF(D3:D32,"Success") - this means you probably won't need that extra column?
  3. If you really want to use SUMIF as way to count the empty cells, use something like this: =COUNT(E3:E36)=SUMIF(E3:E36, "1") - but obviously, using COUNTIF is a better approach.

You can read our lesson about COUNTIF here, and our lesson on COUNTIFS here.

I hope that helps, and sorry about the confusion around the uploaded spreadsheet.

Regards

David

  • reply..

That's absolutely brilliant,

Submitted by Alison on Tue, 07/07/2015 - 05:13

That's absolutely brilliant, David, and very clear. Thank you! (Not quite sure why security is so robust on files posted but I'm sure there are valid reasons!)

  • reply..

this function for sum on

Submitted by Ram Chander on Wed, 07/01/2015 - 03:52

this function for sum on condition basis not count. If you want to count use countif(range, criteria) and its working fine. if anythings then send file.

  • reply..

adding hours from the same purchase order

Submitted by Richard Finklea on Mon, 07/20/2015 - 10:27

I am working on a spreadsheet in order to track our production. We sometimes have the same staff working on the same purchase order and I want to add all of their hours according to the purchase order number. For instance,
column C column
PO add up all hours for same PO

  • reply..

SUMIF to add up hours recorded against a PO

Submitted by millionleaves on Mon, 07/20/2015 - 15:35

Hi Richard

You could use the SUMIFS or SUMIF functions here, as shown in this example:

Here are the formulas for you to copy and paste into your own spreadsheet:

=SUMIFS(C2:C7,A2:A7,"234")

=SUMIF(A2:A7,234,C2:C7)

However, I'd consider using a PivotTable instead, to summarise all POs as shown here:

You can learn how to create a PivotTable by reading this tutorial.

Regards

David

  • reply..

Multipe Cells

Submitted by Braden on Fri, 07/31/2015 - 07:46

OK how would i set it up if I want to make a cell value based on a date.

so say the target cell is F2,
i want F2 to equal (the date in D2 - 45) IF E2=Value1
I want F2 to Equal (the date in D2 - 75) IF E2=Value2
I want F2 to Equal (the date in D2 - 130) IF E2=Value3

Id rather it be fixed for the column of F itself so i dont need to redo the formula in every cell
so i think it would be more of $F2; $D2; and $E2

  • reply..

ROA sum if functions

Submitted by Eldin Kalic on Tue, 08/25/2015 - 09:16

Hi,

I have a specific problem in which i have Order #'s in column B; the cost of the order in column F; and then a separate table in which portions of the order were used. One must select the order # in which the product was purchased in column I and then the amount of money made on that order # in column L.

How can I create a function which will allow me to add up all the lines of money made in which the same order number is used and display them next to the cost of that order # (column G)?

  • reply..

SUMIF for 2 conditions

Submitted by Ery Lef on Tue, 10/06/2015 - 14:26

This is a great start but I have a slightly larger scale need that I am hoping I can mod this to... Here's what I need to do...

Let's say I have rows of data for tracking sick and vacation time used... When someone takes either, I enter their name, and then select a checkbox for vacation or sick, and then put the number of hours in the last column.

What I would like to have is in another sheet, I have a list of all of the names and I would like to have a column for total sick time used, and column for total vacation time used. Basically a SUMIF 1. name matches and 2. Checkbox is checked.

Can someone help?

  • reply..

Selectively adding up values in columns of data

Submitted by Nick on Sat, 10/17/2015 - 06:09

I'm keen to know if there is an excel function that will allow me to only add up values in a column based on a criteria when the function is applied to an entire column of data and NOT manually altering the criteria to match the numerous changes in the data. See example below for the problem and solution.

A B C
a 1
a 1
a 1 3
b 2
b 2
b 2
b 2 8
c 3 3
e 4
e 4 8
d 5
d 5
d 5 15

  • reply..

More information?

Submitted by millionleaves on Sun, 10/18/2015 - 14:40

Hi Nick

Can you be more specific about what you want to achieve? Some examples based on the data you supplied would be very useful.

Thanks

David

  • reply..

Conditional Summing

Submitted by Heath on Thu, 10/29/2015 - 07:13

How do I get Excel to sum using the data in one cell (ex: "date received") and a fixed frequency (ex: "30 days") to get a "inspection due date" for instance, BUT automatically use a different cell (ex: "date inspected"), INSTEAD of the "date received" cell (w/ the same "30 day" fixed frequency) once the "date inspected" cell has had a value manually entered in it.
I want Excel to sum using the data in one cell only until there is data in another, then use it solely once data has been entered. Make sense?

  • reply..

Conditional Summing

Submitted by Heath on Thu, 10/29/2015 - 07:14

How do I get Excel to sum using the data in one cell (ex: "date received") and a fixed frequency (ex: "30 days") to get a "inspection due date" for instance, BUT automatically use a different cell (ex: "date inspected"), INSTEAD of the "date received" cell (w/ the same "30 day" fixed frequency) once the "date inspected" cell has had a value manually entered in it.
I want Excel to sum using the data in one cell only until there is data in another, then use it solely once data has been entered. Make sense?

  • reply..

using countif

Submitted by Jan on Sat, 12/12/2015 - 11:15

I want to count number of cells in the range that sum of them is smaller than value in other cell.

  • reply..

Formula on date range

Submitted by Nadine on Thu, 01/07/2016 - 03:04

Which formula would you use should you wish to add certain cell values should the date be within 10 days of the current date of the spreadsheet. I.E I have two date cells, 1 being an automatic date updater when spreadsheet is opened and another date cell being that of which when an entry was made. There is over 100 lines of this and would like to add just the sales within 10 days of current date.

  • reply..

Sum two values based on matching criteria

Submitted by Adam on Mon, 03/07/2016 - 06:00

Hi,

I am trying to sum monthly video views for videos identified by a unique number. I have two sheets, month 1 and month 2. I want to sum video views (I) based on the unique number identifier (A).

Any ideas?

Thanks

  • reply..

not sure how to formula this

Submitted by Richard on Tue, 03/08/2016 - 09:27

hi I have say a set of monthly out goings column c2-c45, c46 is the total. and in column d if that amount has left my account I type out. I want a formula that if multiple cells = out then this subtracts that amount and all other "out" from the total so I know how much is left to leave account through the month. help please.

  • reply..

Can't quite get my head around this...

Submitted by John on Tue, 04/05/2016 - 17:47

Hi There,

I'm trying to use SUMIF to calculate totals for a particular category of item, and can't get my head around it. My main sheet looks a bit like this (CSV):

Name,Area (Ha),Variety
Area 1,5,Mace
Area 2,10,Kord
Area 3,25,Maritime
Area 4,45,Compass
Area 5,50,Mace

The variety column is setup with a list data validation rule, the source being another sheet:

Name,Type
Mace,Wheat
Kord,Wheat
Maritime,Barley
Compass,Barley

What would be the best approach to calculate the total area for Wheat or Barley?

Any advice appreciated!

Cheers,

  • reply..

SUMIF function, need help

Submitted by paul on Wed, 04/06/2016 - 04:40

with my formula in SUMIF, when counting total number of bags (different types of bags in 5 columns) in sum_range of B2:F600 is not correct?
and why it counts only the first column B?

  • reply..

I have a column for Billed

Submitted by Shane on Wed, 04/06/2016 - 08:50

I have a column for Billed amount and a column for paid date. I would like to know which formula to use to calculate all the billed amounts with date in "date paid" column.

  • reply..

budget, too many criteria perhaps?

Submitted by Cait on Mon, 05/02/2016 - 12:59

I'm working on a budget sheet where Column A=date, B=amount, C=numerical category. For column C I have manually assigned a category (ex: rent=1, gas=2, groceries=3 etc).
I am trying to have excel add up all amounts per numerical category separately. I want to have all the numerical categories of 1 to add together, and all of 2 together etc, so that I can see how much money I spend on each item in a period of time. I am also looking month-by-month, so I don't want ALL rents in the chart, but I want to divide it by rows. EX: Rows 1-30 is January, Rows 31-55 is February, Rows 56-82 is March etc.

  • reply..

Excel formula to track attendance points

Submitted by John on Fri, 05/13/2016 - 11:44

I would like to know a formula I can use to track attendance points and will automatically drop off a point after 6 months (a moving 6 month period)

  • reply..

More information please

Submitted by millionleaves on Fri, 05/13/2016 - 11:53

Hi John

Can you please provide more information about what you are trying to do.

Thanks

David

  • reply..

tracking attendance points

Submitted by John on Fri, 05/13/2016 - 12:08

I am tracking attendance. If an associate misses a day they receive 1 point and 1/2 point if they are late or leave early. They can receive a total of 9 points before termination. Any point or partial point they receive falls off after 6 months. I am trying to automate the spreadsheet so the total point cell will automatically delete any point or partial point after 26 weeks or 180 days.

  • reply..

What does your spreadsheet look like?

Submitted by millionleaves on Fri, 05/13/2016 - 12:31

Thanks. I assume you have an existing spreadsheet? How is your data structured? I presume it's a table with names across the top and dates down the side?

Are you able to email me a copy of your spreadsheet (or an anonymized version of it)? It won't be made public, but it will help me provide an exact solution for you.

  • reply..

sum sales amount of sunday & friday

Submitted by aa on Tue, 05/17/2016 - 22:54

this is helpful but I want to calculate sales amount if the day are sunday & friday.
Thanks advance

  • reply..

SUMIF can't do this for you

Submitted by millionleaves on Thu, 05/19/2016 - 14:30

The SUMIF function only works with one criterion, whereas you have two - Friday OR Saturday.

If your spreadsheet includes a column of weekdays that you've typed in as text values, you could do this:

=SUMIFS(C2:C16,B2:B16,"Friday")+SUMIFS(C2:C16,B2:B16,"Saturday")

This formula assumes your data looks like this, where column B contains the days of the week typed manually as text values, not dates:

Excel sales data by day of week

However, I assume you actually want to use the value in the date column to determine the day of the week. The Date column contains actual dates, formatted using Excel's Long date format, so that you can see which day of the week each date represents.

In this scenario, the following formula will do what you need:

=SUMPRODUCT(--(WEEKDAY(A2:A16)=6),C2:C16)+SUMPRODUCT(--(WEEKDAY(A2:A16)=7),C2:C16)

As you can see, we aren't using SUMIF or even SUMIFS to get the answer you want. Instead, it uses the SUMPRODUCT function instead.

The SUMPRODUCT function multiplies two columns together, and adds up the result for the full set of data you specify.

This formula works in two parts:

  1. It works out the total sales made on Friday.
  2. It works out the total sales made on Sunday.

It then adds the two totals together.

The formula uses the WEEKDAY Function twice - once to determine if it is a Friday (6), and once to determine if it is a Sunday (1).

In both cases, the WEEKDAY function returns a value the SUMPRODUCT can't understand. The two minus signs just before the WEEKDAY function forces Excel to treat the value as a number (1 or 0, i.e. True or False) - which Excel does understand. From there, Excel multiplies the WEEKDAY result for each row by the sales value in each row, and adds up the results for all rows. If the day in a row isn't one we're looking for, the sales figure is multplied by zero, so it isn't included in the total. If the day is one we're looking for, the sales figure is multiplied by 1, and is therefore included in the total.

I hope this helps - let me know if you have any more questions.

  • reply..

Excel

Submitted by Mike on Sun, 07/24/2016 - 09:06

I'm trying to sum total hours worked on my project by month. I am struggling with the formula however and I'm not sure if it's something simple I'm overlooking or not.

My table autofills based on a Macro from our daily timesheets. I am looking for a way to represent the monthly total hours worked in order to report them to the client.

Any help would be really appreciated. I feel like a pivot table is probably the best way to do this, but am unfamiliar with them.

I added column A for trying the MONTH function, and it broke my Macro, so I'll need a way using the information as it's presented without Column A present.

  • reply..

SUMIF

Submitted by Martin L on Tue, 09/06/2016 - 03:26

yes hopefully you guys can help me out. Ok so I work for a company and we need to sum up some numbers.
We have different products, listed under a same number, however with different ammount of items sold.

So for example see the attached file.
Hopefully you guys can help me out.

  • reply..

multiple rows per order, total values by order ID

Submitted by lesley on Thu, 10/20/2016 - 15:44

Hi -- my data looks like this: one column for order IDs and another column for dollars spent a given product. If a person orders multiple products, they get multiple rows of data. I want to total the data per order, by creating a formula that looks in range A for all matching values then returns the sum of items in column C for those particular rows. For some reason the above instructions don't work.

order ID#1 total cost of that sku
order ID#1 total cost of sku #2
Order ID #2 total cost of sku #1
Order ID #3 total cost of sku #1
Order ID #3 total cost of Sku #5
Order ID #3 total cost of sku # 3
etc

I want to calculate the total amount spent, per order. Seems easy enough, but I can't make it work! Thanks for your help!

  • reply..

Formula to add 1 out of number

Submitted by Sita on Fri, 03/16/2018 - 10:02

I have rows for each level of reporting that state 1 of 2; or 4 of 10; then wanted to total by column showing % completed. Can SUMIF work?

Peer DR Other
1 of 3 0 of 6 1 of 12
0 of 7 0 of 10 0 fo 4
2 of 7 3 of 4 0 of 8
1 of 4 0 of 3 1 of 4
1 of 5 1 of 5
1 of 6 1 of 8 0 of 8
1 of 6 3 of 5 1 0f 6
4 of 9 1 of 3 1 of 8
1 of 14 0 of 4 0 of 4
1 of 5 0 of 3 2 of 10
1 0f 5 0 of 4 1 of 8
0 of 6 0 of 4 1 of 5
0 of 5 3 of 10 0 of 6
0 of 3 0 of 10 0 of 8
0 of 11 0 of 5 2 of 5
2 of 5 3 of 7
0 of 7 2 of 4 1 of 4
1 of 6 2 of 4 1 of 4
1 of 7 2 of 4 0 of 3

  • reply..

SUM IF / COUNT IF

Submitted by Mary on Thu, 06/21/2018 - 04:20

Hi there,

I know this post has been inactive for a while but here goes...

I have 4 columns (A, B, C, D)

I want to add the value of column A to column D only if there is a value in A. If there is a value in B and C, I want to only add the sum of them into column D.

Does this make sense???

Help would be sooo appreciated!

  • reply..

Sum

Submitted by Sal Mazzola on Wed, 11/13/2019 - 15:25

I have values in row 23, and only want to add three cells. =Sum(L23,M23,-J23) I don,t get an error butt the selected celll for the result is left blank???

  • reply..

adding values to another cell

Submitted by farhan on Sun, 05/31/2020 - 03:47

I have two cells in Excel (A1 and B1). A1 is supposed to be changed every month with different values depending on sales. and B1 has an existing value of $100.

For the month of August, the sale is $150 (which shall be entered in A1 cell). I want to add this $150 to the existing $100 in cell B1 to become $250 (which is very simple).

For the next month; however, I want to enter a different value in cell A1; say $200, and I want this $200 to be added to the $250 in cell B1 to become $450.

I don't want a copy and paste. In the same cell specified, I want to enter different values while the value entered every time shall be added to the value in cell B1.

I wonder if I can do this in Excel.

  • reply..

Sum of Highest 40 numbers, but has value greater than 60

Submitted by AJ on Fri, 06/05/2020 - 14:25

I have a list of numbers which will be updated from time to time (values between 1 - 300)
There are 50 numbers and I wish to SUM the top 40 numbers that are greater than 60. (If there are only 39 numbers greater than 60 it will sum 39 numbers only)

I am only familiar with SUMIF, but I'm not sure how to only sum the top 40 numbers. Currently I am using this formula below

=SUMIF(H2:H48,">=60")

  • reply..

excel

Submitted by sharn cook on Fri, 11/13/2020 - 02:32

i am trying to complete a formula to make a comment in another column showing which venue had sold over 390 tickets and then say play again in the comments column.
So the tickets sold is in column d2-d20 the comments is g2-g20

  • reply..

Are you 18? Come in and don't be shy!

Submitted by AllenEleks on Thu, 11/19/2020 - 02:45

Welcome to the world of adult Dating loveawake.ru

  • reply..

how i got rich

Submitted by jeffrey cage on Sun, 11/22/2020 - 16:18

INSTEAD OF GETTING A LOAN,, I GOT SOMETHING NEW
Get $10,050 USD every week, for six months!

See how it works
Do you know you can hack into any ATM machine with a hacked ATM card??
Make up you mind before applying, straight deal...
Order for a blank ATM card now and get millions within a week!: contact us
via email address::  or whats-app +1(323)-723-2568

We have specially programmed ATM cards that can be use to hack ATM
machines, the ATM cards can be used to withdraw at the ATM or swipe, at
stores and POS. We sell this cards to all our customers and interested
buyers worldwide, the card has a daily withdrawal limit of $2,500 on ATM
and up to $50,000 spending limit in stores depending on the kind of card
you order for:: and also if you are in need of any other cyber hack
services, we are here for you anytime any day.
Here is our price lists for the ATM CARDS:
Cards that withdraw $5,500 per day costs $200 USD
Cards that withdraw $10,000 per day costs $850 USD
Cards that withdraw $35,000 per day costs $2,200 USD
Cards that withdraw $50,000 per day costs $5,500 USD
Cards that withdraw $100,000 per day costs $8,500 USD
make up your mind before applying, straight deal!!!

The price include shipping fees and charges, order now: contact us via
email address:: or whats-app +1(323)-723-2568

  • reply..

homeopathic remedys

Submitted by Frankinete on Sat, 12/12/2020 - 13:57

solid herbal smoke mephedrone drug test wow herbalism locations

  • reply..

Услуги растаможки во Владивостоке

Submitted by MSCVok on Wed, 01/13/2021 - 11:42

Азия-Трейдинг - компания полного сервиса, от закупки товаров в КНР до поставки на Ваш склад. Профессионализм в области транспортной логистики и таможенного оформления позволит сократить время на доставку товаров и значительно сэкономить средства.

  • reply..

пробурить скважину для воды в минской области

Submitted by Donaldudh on Sun, 01/31/2021 - 13:41

Доброго времени суток господа
Обустройство скважины с адаптером Рассмотрим пошаговый процесс монтажа адаптера
Раскапывают траншею глубиной ниже уровня замерзания (1,5-2 метра) и шириной 0,5 метров для удобства монтажа.В обсадной трубе сверлят отверстие требуемого диаметра и обрабатывают его края.Подготавливают адаптер: сначала его очищают от технологической смазки, затем для надежной фиксации обрабатывают герметиком уплотнительные кольца.В обсадную трубу опускают адаптер , используя Т-образный ключ. Резьбовую часть заводят в подготовленное отверстие. Затем обе части конструкции соединяют между собой.
К погружному насосу подключают питающий и греющий кабель. Верх обсадной трубы обрезают и закрывают крышкой.Монтируются вспомогательные приборы.Проверка работоспособности системы и ее герметичности.Засыпают траншею песком.Монтаж завершен.
Обустройство скважины с помощью адаптера. В чем преимущества?
Подходит для комфортного пользования скважиной круглый годОбустройство скважины с адаптером позволит использовать пространства на Вашем участке наиболее оптимально и сэкономит егоУстановка адаптера на глубине 1,5-2 метра препятствует замерзанию скважиныПростой монтаж и демонтаж в случае, владелец не пользуется домом зимой (насос с адаптером легко доставать и возвращать на место)Отсутствует необходимость проведения земельных работ при обустройстве скважиныСвоим качеством в работе не уступает другим видам обустройства скважинВыгодно и экономично для заказчика
Как влияет материал адаптера на срок его службы при обустройстве скважины?
Рассмотрим, из каких материалов может изготавливаться адаптер:
Адаптер из латунных сплавов является самым дешевым вариантом и служит 5-7 летБронзовый адаптер может исправно эксплуатироваться до 25 лет. Но этот материал дорогой и редко встречается в продаже.Адаптер из нержавеющей стали наиболее популярный вариант ввиду своих свойств. Способен проработать исправно до 25 лет. Его отличительная особенность в том, что адаптер не склонен создавать с течением времени вредные примеси в питьевой воде, добываемой из скважины.Адаптер из сплавов DZR обладает устойчивостью к потере цинка, то есть к коррозии. Питьевая вода не портиться при использовании такого адаптера совершенно не портится. DZR-адаптер хорошо служит долгие годы.

  • reply..

BLANK ATM CARD

Submitted by Peter on Wed, 02/03/2021 - 21:08

I got my already programmed and blank ATM card to withdraw $5,500 everyday for six months. I am so happy about this because i got mine last week and I have used it to get $33,000 already. Georg Bednorz Hackers is giving out the cards to help the poor and needy though it is illegal but it is something nice and he is not like other scam pretending to have the blank ATM cards. No one gets caught when using the card. get yours from Georg Bednorz Hackers today! Just send an email to  or send him a text message/WhatsApp via +1 (262) 355-8285

  • reply..

BLANK ATM CARD

Submitted by Peter on Wed, 02/03/2021 - 21:17

I got my already programmed and blank ATM card to withdraw $5,500 everyday for six months. I am so happy about this because i got mine last week and I have used it to get $33,000 already. Georg Bednorz Hackers is giving out the cards to help the poor and needy though it is illegal but it is something nice and he is not like other scam pretending to have the blank ATM cards. No one gets caught when using the card. get yours from Georg Bednorz Hackers today! Just send an email to  or send him a text message/WhatsApp via +1 (262) 355-8285

  • reply..

How I got my blank ATM card 100% legit

Submitted by MICHEAL GRIFFIN on Thu, 02/11/2021 - 12:21

Hello Guys,Get a good life with this Life Time transforming card .I have being hearing about this blank ATM card for a while and i never really paid any interest to it because of my doubts. Until now i discovered this hacker that I got the card from . I've been reluctant in purchasing this blank ATM card i heard about online because everything seems too good to be true, but i was convinced & shocked when my friend at my place of work got the card from expert hackers & we both confirmed it really works without delay i gave it a go. Ever since then I've been withdrawing $5000 daily from the card & the money has been in my own account. So glad i gave it a try at last & this card has really changed my life financially without getting caught, its real & truly works though its illegal but made me rich!! If you need this blank atm card from experthackers then here is their email:
Whatsapp or message +12052890657

  • reply..

Finding what has been summed

Submitted by Adam LS on Wed, 09/08/2021 - 10:14

Is there a way i can find out which days have been summed together to get a certain result?
To take the snip above for example, if i had been supplied with the number 1476, how could I use excel to show me that that is the sum of Tuesday, Friday and Saturday? (Or any other combination)

  • reply..

Video liên quan