Comments on: How to use SUMIF function in Excel with formula examples

If you are faced with a task that requires conditional sum in Excel, the SUMIF function is what you need. It is a really great function that can help you make sense of an incomprehensible set of diverse data. Instead of adding up all numbers in a range, it lets you sum only those values that meet your criteria. Continue reading →

Comments page 9. Total comments: 346

  1. First Example:
    A1 (Date); B1 (Bucket Size of 2.5 gallons); C1(Seconds To Fill Bucket); D1 (Gallons Per Minute).

    Quest:
    A1 (Date); B1 (Seconds To Fill Bucket);C1
    Gallons Per Minute

    Hi,
    I’m cyber (computer) un-savvy . . .
    I succeeded with three cells, in that, that equation =SUM(B1/C1*60) imposed in D1 will provide me with a correct answer. . Note, A1 is just the date of occurrence and is NOT necessary for the equation.
    My quest is to formulate the equation so that it only involves two cells. B1 represents the constant (2.5 gallon bucket) that is divided by a variable (time of seconds to fill the bucket) that is entered into the cell, and C1 is the hiddeb equation that displays the answer (gpm). In essence, the equation is 2.5/B1*60=gpm . . . 2.5 is a constant bucket size, B1 is the variable of time (seconds to fill the bucket), *60 is a constant (60 seconds) = ANSWER (gallons per minute)
    I have partial success, in that, C1’s equation =SUM(2.5/A1*60) functions if A1’s value is less or greater than 0 (zero). However, with the aforementioned equation, if the B1 value is 0 (zero) or left empty (implication that a sample was not obtained for that date) then there is an annoying and always present cell admonition of #DIV/0!.
    Can you help???
    Blessings,
    Tim

  2. I have never commented on such a forum before. However, thought you deserved truck loads of positive feedback on a concise, easy to understand, educational post. Thank you.

  3. I wish to set up a criteria in such a way that it adds numbers in a range but the criteria excludes certains conditions within the criteria range.

    Row A B
    1 212200 10
    2 218000 20
    3 214000 40
    4 215000 50
    5 216000 60
    6 217000 80

    I this example I want to add up column b(b1:b6), criteria range (A1:A6), criteria is anything => 212200, but <=217000 and exclude 215000 to 216000.

    Please help...

  4. Hello, I cam trying to create a formula where I have 2 cells - one is the gross weight and the other is the dimensional weight. sometimes it differs on which one is greater. I need to create a sum of multiplying the charge by which ever the weights is greater. Can you please help me?

  5. I am trying to work on a sheet where i have customer sales details of two products from which i want to calculate which customer has purchased what product in total. for one i cn calculate using "sumif" formula but its difficult to change the for formula for each customer.. can you please suggest me

  6. hi
    i have row with the following data in the cells:
    A3=20, B3=50, C3=PAID, D3=60, E3=PAID, F3=60, G3=PAID, H3=20, I3=40, J3=60
    I WANT TO SUM THE FOLLOWING CELLS: A3, C3, E3, F3, J3
    AS A RESULT I GET AN ERROR NAMED "VALUE".
    HOW CAN I SUM THESE CELLS WITHOUT GETTING AN ERROR.
    THANK YOU,
    ZVI

  7. Hi
    I am needing a formula that sums only if both cells have a value.
    And that answer will be multiplied by another cell
    For example =a25+b25*g28
    The formula is put into c28, I don't want anything appearing in c28 until both a25 and b25 contain a number or value, It needs to wait to add until I put a number into both cells,
    Thank you soo much for your time,
    I am useing 2013 excel

  8. Hi

    I'm trying to set up a prepayments schedule and I have the start date, end date, amount and daily rate shown is separate cells for an invoice. I have 12 further columns for each month of the year. I would like to have the invoice amount split between the months according to the number of days relevant to the month. eg an invoice may run from 13th of 1 month to the 12 of another. is there a formula that will calculate the number of days relevant for the month and multiply this by the daily rate?

  9. I am trying to create a formula that puts in a cell an invoice amount based on different date ranges.
    The invoice amount will be $1000 if the invoice date is between 2 date ranges in a month say August (100 %)
    The invoice amount will then drop to 900 if between 2 date ranges in September(90%)
    The invoice amount drops to say $800 if between 2 date ranges in October.

    1. Hello Michael,

      You can use the following kind of formula for your task:
      =IF(AND(A1>DATE(2015,8,1), A1<DATE(2015,8,31)),1000,IF(AND(A1>DATE(2015,9,1), A1<DATE(2015,9,30)),900,IF(AND(A1>DATE(2015,10,1), A1<DATE(2015,10,31)),800, 1000)))

      It allows you to list several conditions for different values that you want to show. Please also see for examples of using dates in your formulas.

  10. I don't think this site is being monitored anymore. I don't see any answers.

    Hello?

    1. Hello Jeannie,

      We apologize for the delay, it takes time to look at all the comments we get and provide a solution. Please find the formula for your task above.

  11. Hello,

    Column A i have date starting from 1 to 30 up to rows 30...

    Column B i have Day from Monday to Saturday up to rows 30...

    Column C i have Traget hours from 16 to 50 hours with if logic function base on value of column F ( Total of column D and E )

    Now i want to apply sumif formula at the end for day for Example if the day is Friday then ...

    =SUMIF(A1:A30,"Friday",B1:B30 )

    But formula not given any error or and doinn sum also..

    But same formula when I apply in column those have direct value ( not coming from if fuction ) it's working properly.

    Thanks if anyone can give anwser

    1. Hello Hari Mohan,

      As you have dates in column A, days in column B, and the hours to sum in column C, your formula should have columns B and C as the range and sum range instead of columns A and B, i.e.:
      =SUMIF(B1:B30,"Friday",C1:C30)

  12. Hello,
    Can you help me regarding sumif formula which not working properly.
    Name of DAy form Sunday to Saturday in Column A and number of working hours in column B ( and column B all values are base on IF formula ).
    When I want to apply SUMIF formula its not working because my target hours base on if formula in column B.

    Same formula when I apply in a simple way then its working properly.

  13. Your help with my problem would be appreciated.
    It is probably a simple code, but I'm struggling with it.

    Every 4 years I have to calculate how many precinct committee people a precinct can have at each site.

    For less than 1,000 voters, it is 1 pair
    For more than 1,001 voters, it is 2 pair
    For more than 2001 voters, it is 3 pair

    The form is a 3 columns: first column is the name of the precinct, 2nd column is the total voters and 3rd column shows how many precinct committee people is allowed.

    Precinct Name Total Reg Voters Allowable Pairs
    Sumter Place Rec Room 3432

    Can you help?

    1. Hello Jeannie,

      I'm really sorry that you had to wait for the formula.

      You can use the IF function with a combination of your conditions for your task:
      =IF(B2<1001,1, IF(B2<2001, 2, 3))

      You need to enter this formula into column C and copy it across the column.
      Here B2 is the cell with the "total reg voters". If there are fewer than 1001, the formula will show 1, if there are fewer than 2001, it will show 2, otherwise it will show 3.

    2. Since I have not heard back on my question, I can only assume
      that there isn't a formula?

      Jeannie

  14. OK. My prior post somehow did not come across what I had written. Trying again

    Create a formula with sumif(s) using >0 or <0 in the equation. Keeps returning either a "false", or sum of entire range.

    sum range = BSJun_Act
    criteria = BSMap_to,"Interco"

    1. Hello Diana,

      It sounds like you need to use the SUMIFS function that lets you specify several conditions. E.g.
      =SUMIFS(B15:B27,A15:A27,"Interco",B15:B27,">0")
      The range A15:A27 is checked for the word "Interco", B15:B27 is checked for the condition ">0".

      You can use named ranges instead of the range references by spelling them this way:
      =SUMIFS(BSJun_Act,BSMap_to,"Interco",BSJun_Act,">0")

      If you get an error, please make sure both ranges are of the same size.

  15. I am trying to create a sumif of sumifs formula using named ranges and using either a >0 or 0",SUMIF(BSMap_to,"Interco",BSJun_Act)0)

    IF(Sumif(BSMap_to,"Interco")>0,SUMIF(BSMap_to,"Interco",BSJun_Act))

    It seems to work if I use an actual range like A15:A27, but I can't seem to find the answer to get this formula to work using named ranges where the range is not in a series.

    Thanks for your help!

  16. Hi, Svetlana,

    I am kind of trying to right down a formula that would count me the following condition:

    - if the certain cells sum (lets say B2 to Q2) would be more or equal to 80, then the sums of the cells B2:B3 should be multiplied by 2 and to it cells B4 and B5 should be added, if the cells sum (B2 to Q2) would be more or equal to 160, then the sums of the cells B2:B3 should be multiplied by 3 and to it cells B4 and B5 should be added, if neither are correct (that is the value of the cells sum is less than 80) then the formula should return value D5.

    Even better formula would be:

    - when the sum of the cells reaches number 80, then the sum of the cells B2:B3 should be multiplies by 2 and to it cells B4 and B5 added, when reaches 160 - multiplied by 3 and to it cells B4 and B5 added. If the sum is less then 80, then the value should be D5.

    I can't get correct formula anyhow...

    Would really appreciate Your insights on this one. Thanks in advance.

    1. Hello Justin,

      You can use the following formula for your task:
      =IF(SUM(B2:Q2)>160,SUM($B$2:$B$3)*3+SUM($B$4:$B$5),IF(SUM(B2:Q2)>80,SUM($B$2:$B$3)*2+SUM($B$4:$B$5),D5))

      If you'd like the summed ranges to shift as you copy the formula, please make the references relative, i.e.:
      =IF(SUM(B2:Q2)>160,SUM(B2:B3)*3+SUM(B4:B5),IF(SUM(B2:Q2)>80,SUM(B2:B3)*2+SUM(B4:B5),D5))

  17. How to sort data when cell are merged and wraped

    1. Hello,
      You can sort merged cells only if they are of the same size: select them and click the Sort icon.
      Otherwise you need to unmerge all cells in the range before sorting.

  18. Hi?

    Kindly help i have data of overheads with diferent dates now i want to sum up using the sum if functions overheads per month eg

    Electricity 2/04/2015 $200
    electricity 3/04/2015 $215
    courier cost 2/04/2015 $2

    Basically i want to come up with a spreadsheet that can be able to sum up overheads say march electricity was eg $700 travel Expenses may $600

    Thanks in advance

    Bismark

    1. Hello Bismark,

      If you want to consider just the month, you can use one of the following formulas to calculate the expenses, e.g. in March:

      =SUMIF(B1:B3,">="&DATE(2015,3,1),C1:C3)-SUMIF(B1:B3,">="&DATE(2015,3,31),C1:C3)

      =SUMIF(B1:B3,">=03/01/2015",C1:C3)-SUMIF(B9:B14,">=03/31/2015",C1:C3)

      =SUMIFS(C1:C3,B1:B3,">=03/01/2015",B1:B3,"<=03/31/2015")

      If you want to consider the value in column A as well, e.g. "electricity", please use the SUMIFS function:
      =SUMIFS(C1:C6,B1:B6,">=03/01/2015",B1:B6,"<=03/31/2015",A1:A6,"electricity")

  19. i just want to sum all in one merge cell only
    Thanks You;

  20. 1 Vikash 50 ?
    50
    50
    50
    2 Mahesh 100 ?
    100
    100
    100
    3 Rakesh 500 ?
    500
    500
    I want to sum these values where is the question Mark (?) in excel with single formula.Please help me.

  21. I have an Excel 2010 worksheet containing a separate "$ amount" column for each of 3 entities (column headers: MD, MDM & MDW).
    The data is rows of direct debit amounts from the company's bank accounts that are specified in 3 "Frequency"-header columns ("MTHLY", "QTRLY" or "ANNUALLY").
    I have auto-summed each entity column, so have an overall direct debit total for each of the 3 entities (MD, MDM & MDW).
    However, I need to auto-calculate the total - OF ALL 3 ENTITIES TOGETHER - for each of the 3 frequency columns (so $ amount total for "monthly", "quarterly" & "annual" direct debits)
    I have wasted the last couple of hours trying to find a formula for this & have tried a variety of SUMIF cell combinations/formulas, but cannot get this to work!!!!
    Can you please advise?

  22. Hi,

    I have a spreadsheet where I am trying to work out a formula to tell me how many cells have a date that is older than a year from today. At the bottom of the training date column I'd like to see a figure that tells me how many of the cells have a date older than one year.
    Are you able to help please?

    Training Date

    01/06/2014
    03/06/2015
    01/01/2014
    01/10/2014
    05/06/2014
    09/08/2014
    01/03/2014

    1. Hi Rae,

      You can use the DATEDIF function to calculate the number of complete years between the dates in, say, column A and TODAY(). And then add up those that are equal to or greater than 1:
      =SUMPRODUCT((DATEDIF(A2:A100, TODAY(),"y")>=1)*1)

  23. I'm not sure if I have the right topic, but I'm trying to subtract the larger number from 2 cells (not a range, eg. A1 and A3) and subtract them from a number in cell A5. I just can't seem to find a formula that works.

    1. Hi Sean,

      Here you go:
      =IF(A1>A3, A5-A1, IF(A3>A1, A5-A3, ""))

      Just notice that the formula will return an empty string if A1=A3.

      1. Thank you so much!

  24. How would I get the sum formula to sum two numbers. I need the smallest number out of Coulum O through R and need that to be added with the number from column S. I just need the sum formula to decipher the smallest number from the range of colums.

    1. Hi beth,

      You can use a formula similar to this:
      =MIN(O2:R10)+MIN(S2:S10)

  25. I am wanting to sum cumulative values across cells if the last cell in each formula is greater than 0.

    e.g sumif(C18:F18,F18>0)
    F18 being the last cell in the formula, and the next one would be (C18:G18,G18>0) and so on

    This is for an actuals vs forecasted spend graph of invoices.

    1. Hi Sarah,

      I believe you can use this one:
      =if(F18>0, SUM($C$18:F18), "")

      1. That works perfectly, thank you! :)

  26. I am trying to sum numbers that occur at specific times (4/20/15 2:01 PM, 2658
    4/20/15 2:04 PM, 2268, etc.)
    but I only want to sum them if they occur during another time window specific in a third column. Is there a way to do this? The goal is to try to take random time periods and make them more uniform (15 minute intervals) by summing results during the random time period.

    1. Hi Rebecca,

      You can try using the COUNTIFS formula similar to this:

      =COUNTIFS(A1:A11, "04/20/2015 2:01:00 PM", B1:B11, 10)

      Where column A contains times, B - intervals, and 10 is the time interval you want to count.

  27. I want to find the sum of the QUANTITY of any row that has a particular text string present in 1 or more columns of that row.

    Example:

    Row 1 has a quantity of 1,000 and has the word:
    "Apple" in 2 of the 5 criteria columns
    "Plum" in 1 of the 5 criteria columns

    Row 2 has a quantity of 2,000 and has the word:
    "Apple" in 1 of the 5 criteria columns
    "Plum" in 0 of the 5 criteria columns

    The resulting quantity for "Apple" would be 1,000 + 2,000 because both Rows 1 and 2 have the word "Apple" in at least one of the 5 criteria columns.

    The resulting quantity for "Plum" would be 2,000 because only Row 2 has the word "Plum" in any of the 5 criteria columns.

    1. Hi Dianne,

      You can add up 5 SUMIF functions, like this:

      =SUMIF(B2:B100, "apple", A2:A100) + SUMIF(C2:C100, "apple", A2:A100) + SUMIF(D2:D100, "apple", A2:A100) + etc.

  28. Hi,

    Reg SUMIFS formula.

    My criteria range include numbers but they are in text format. Such as 00001,00002. Since these numbers are coming from ERP system report, format changing is quite difficult. Please let me know a solution for this. Sum range is OK. My formula is not working because criteria range is in text format. please help.

    1. Hi Rasika,

      Did you try enclosing those text-numbers in double quotes like usual text values? E.g.:

      =SUMIF(range, "00001", sum_range)

  29. I am trying to match amounts from two different datasets. I have cell A1(GL acct #) B1(trial balance $ amt) and then from an entirely different system with the exact same format A1(GL acct #) B1(trial balance $ amt). These GL accts are mapped to one another, the issue with my sumif is that it will pull the return the same $ for multiple lines. This is because in some cases 4 different GL accts from system 1 map into only 1 GL acct from system two.

    I want my sumif to only return the amount ONCE, as opposed to every time it comes across a match. A nested sumif is my thought, saying something like IF((___ has not yet appeared)then(sumif(yadayadayada))else(return(n/a)) but i cant figure it out.

  30. I have a column of a range of dollars and another column with a range of hours.
    I want to have a function whereby if the dollars fall between two values then it returns of a sum of the corresponding hours.

    I.E if the $ are equal to or between $3000 and $5000 then add the hours in the Hours column which correspond to the dollar rows.

    I am sure this can be done - but how?

    Best wishes

    RJ

    1. Hello Rupert,

      You can use the SUMIFS function to sum values in the column with hours when the dollar values fall between two numbers:
      =SUMIFS(B12:B20,A12:A20,">3000",A12:A20,"<5000")

      Here column B is the one with hours and column A contains the range of dollars.

  31. Hi,
    I need an Excel VBA function to look for numbers in range of cells that contain both numbers and text and sum those numbers.

  32. Hi,
    I want to look for any cell that contains number in celles range and sum thos numbers?

  33. Hi, I have three columns, A, B and C:

    Col A
    36
    0
    253

    Col B
    2
    0
    3

    For Column C, I need a formula that will provide the sum of the corresponding cell in Column B, except if the corresponding (row) number in Column A is between 0 and 43. In that case, the number in Column C should be a "1".

    So instead of Column C being 2, 0, 3, it would be 1, 0, 3. Can you possibly help??

    Thank you in advance.

    1. Hello Lory,

      If we understand your task correctly, here is the formula you need:
      =IF(AND(A1>0,A1<43),1,B1)

  34. Hi,
    I am trying for a formula where I am summing of cells and it 8th not showing the exact value due to negative value. I don't want to count the cell in negative or #ref!.Please help me with formula.

    1. Hello Deepak,

      You can use the following formula to exclude negative numbers:
      =SUMIF(B1:B8,">=0",B1:B8)
      Here B1:B8 is the range of cells you want to sum.

  35. First, let me say thanks for and good work on a well written tutorial. I found this page by googling "excel sumifs not equal to text" and it has been helpful, but I'm not quite to what I need.

    Suppose in your example "Using comparison operators with cell references" that you wished to sum items not in a range instead of not in a single cell - how would the syntax change instead of ""&F1 ? If column G had a list of 3 different fruit, is there a way to do G1:G3?

    I tried a couple things, and they didn't seem to work. I could achieve the desired result by using a sumifs statement with each individual cell called out in an additional criterion, but if I had a list with more than just a few cells, adding several criteria would quickly become tedious. Any advice would be great. Thanks!

    1. Hi Greg,

      Thank you so much for your kind words. I am afraid I cannot suggest any way other than a SUMIFS formul such as:
      =SUMIFS($B$2:$B$9, $A$2:$A$9, "<>"&G1, $A$2:$A$9, "<>"&G2, $A$2:$A$9, "<>"&G3)

      1. Thank you for the response! I've used SUMIFS to get this affect, but my concern is that if column G has 20 entries, the formula will become rather long (or, because the entire column cannot be excluded, adding an entry will require a change to the formula). After some further research, it looks like I'll be able to get there with a complicated SUMPRODUCT statement including ISNUMBER and MATCH. I will try that for my application and see where it goes.

        Thanks again!

  36. Is this possible?
    I want to sum amounts from criteria contained in more than one column:
    1. Criteria = is equal to a credit card number (This column that contains the credit card number)
    2. Criteria = is equal to “NO” (This column contains [yes or no] and it indicates whether or not the card has been paid)
    3. Sum_Range = I want to include the “No” criteria sums

  37. I have a table with a column for insurance company, a column for type of insurance, and a column for the premium paid for that policy. I'd like to do a conditional total IF Column A = "Erie" AND Column B = "Auto" THEN print the sum of those cells in Column C that meet both criteria. I'm thinking that I need to nest functions, but not sure the correct syntax for excel. Thanks!

    1. Hi Shawnda,

      You can use the SUMIFS formula similar to this:
      =SUMIFS(C2:C100, A2:A100, "Erie", B2:B100, "Auto")

  38. Am very grateful 1,000 000 thanks...

  39. I got it... thanks.

  40. I am working on a spreadsheet that tallies up donations (cans) per day but I want to have one cell tally for every 10 cans donated they receive 1 pt.

    What formula should I use?

    Thanks.

  41. Hi,
    Using your given example.

    Is it possible to copy to a cell all the products with the same quantity?

    In order to list every product with quantity higher then X.

    Thanks in advance.
    Best regards,
    José

    1. Hi Jose,

      The easiest way to go would be filtering your data by quantity and copying the records you get.

      You can also use the VBA code that one of our developers wrote for you; please follow these steps:
      - Open your Excel file
      - Press Alt+F11 on your keyboard;
      - Double-click the sheet name in the list and paste the code below, but change the reference to your data range and to the cell with the quanity criterion:

      Private Sub CommandButton2_Click()
      Range("F1:G10").Select
      Selection.ClearContents
      i = Range("D2").Value
      Range("A1:B10").Select
      Selection.AutoFilter
      ActiveSheet.Range("$A$1:$B$10").AutoFilter Field:=2, Criteria1:=i
      Range("A1:B10").Select
      Selection.Copy
      Range("F1").Select
      ActiveSheet.Paste
      Application.CutCopyMode = False
      ActiveSheet.ShowAllData
      Selection.AutoFilter
      End Sub

      - Click Run.

      You can see a sample spreadsheet with this solution here.

  42. Hi,
    This is really very much helpful.

    I have a data of a period of one year(with Date mmddyyyy format). Is it possible to use the SUMIFS function to get the first criteria as month against the date range and one second criteria to get sum of expenses

    Thanking You...
    Tonis

    1. Hello Tonis,

      If we understand your task correctly, you can use either SUMIF function:
      =SUMIF(A2:A33,">="&DATE(2015,2,1),B2:B33)-SUMIF(A2:A33,">"&DATE(2015,3,1),B2:B33)

      or SUMIFS function:
      =SUMIFS(B2:B33,A2:A33,">="&DATE(2015,2,1),A2:A33,"<"&DATE(2015,3,1)) Here column B is the one with expenses, and column A contains the dates.

  43. is it possible to use 'sumif'formula to sum the similar colored cells?

  44. Hello

    I want to sum invoice ammounts that our clients owe us. Some are due payments in 90, 60 or 30 days from invoice date. The fields are: client name, invoice date, total invoice amount and invoice state. Example: ACME, $3.567, 12-25-2014, pending.

    Is there any way to have update owed amounts based on today() that should be paid because 90 days since invoice date have occurred?

    Thank you

    1. Hello Tomeu,

      You can use Conditional Formatting to highlight the necessary amounts: create a rule and choose to "Use a formula to determine which cells to format", enter the following one:
      =AND(TODAY()-B2>90,D2="pending")
      Here B2 is the invoice date and D2 is the invoice state.

      You can also use a helper column with the following formula to show the amount if both conditions are true:
      =IF(AND(TODAY()-B2>90,D2="pending"),C2)

  45. how can I use SUMIF to add numbers in a cell with out the letter as in 8T, here is my formula so far

    =SUMIF(C10:AG10,"<8",C13:AG13) here is the problem.

    in C10-AG10 I have a number 8 or less, in C13-AG13 I have a number and letter 8T, 7T, 6T, 5T, 4T,... down to 0.5T and I need to add the number but not the "T", how can I modify the SUMIF statement to do this, the cells are formatted Text, I have put the cells to number but if I put in the "T" then it doesn't work, I need the letter to identify what kind of number it is.

    1. Hello Mac,

      Try the following array formula (press Ctrl+Shift+Enter to complete it)
      =IFERROR(SUM( --(C10:AG10<8)*(IF(LEN(C13:AG13)>1,LEFT(C13:AG13,LEN(C13:AG13)-1),0))),0)

      If it is not the case, you can send your workbook to support@ablebits.com. We’ll look into the issue and try to help.

  46. i have a table basically column A date, column B cash, column c Check. I need to look at column B & C in same row and sum all based on whether column A's year text only, like 2015.so it would give me a total of all payouts in 2015 only. the date column is formatted 1-jul-2015 with multiple years being listed.

    date cash check
    1-jul-2015 50.00 0
    1/jan-2014 0 100
    1-feb-2105 0 75

    total for 2015 should be 125.00

    1. Hello Brian,

      As SUMIF and SUMIFS sum values only if the ranges are of the same size, you can add two SUMIFS:

      =SUMIFS(B1:B3,A1:A3, ">=1/01/2015",A1:A3, "<=12/31/2015")+SUMIFS(C1:C3,A1:A3, ">=1/01/2015",A1:A3, "<=12/31/2015")
      Here B1:B3 is the range with cash amounts, C1:C3 is the range with check amounts, and A1:A3 is the range with dates.

  47. I'm trying figure out what formula to use. I have two columns of numbers. I want a formula that does the following:

    Whatever cell in column A is in the same row as column B, when the sum in column B whichever cell is greater than or equal to the value of another cell). ***SUM(B1:B-whichever cell is greater than or equal to the value of another cell)***

    Thanks!

    1. Hello Grant,

      Could you please send a sample worksheet with the data and the expected results to support@ablebits.com? It will help us understand your task better.

      Please include the name of the blog post and the number of your comment.

  48. Hi,
    I am working on a financial model, and i have may cash flow mapped monthly.
    Now i would like to analyze the information on a trimester basis.
    Please can you assist with a formula that allows me to sum the monthly amount to a trimester amount.

    Regards

    1. Hello Thany,

      You can create SUMIFS formula and use date ranges as criteria:
      Trimester 1:
      =SUMIFS(B1:B3,A1:A3, ">=1/01/2015",A1:A3, "<=01/10/2015")

      Trimester 2:
      =SUMIFS(B1:B3,A1:A3, ">=1/11/2015",A1:A3, "<=01/20/2015")

      Trimester 3:
      =SUMIFS(B1:B3,A1:A3, ">=1/21/2015",A1:A3, "<=01/31/2015")

  49. I NEED HELP WITH A FORMULA.
    I HAVE A SPACE THAT IS FILLED WITH AN AMOUNT.
    BELOW I HAVE ANOTHER SPACE THAT HAS THE TOTAL.
    INBETWEEN THOSE SPACES I NEED TO BUT A SQUARE WITH THE FORMULA THAT HELP ME ADD A CERTAIN NUMBER IF THE TOP SPACE REACHES TO OR OVER THAT CERTAIN NUMBER.
    I.E

    SQUARE ONE-5.00
    SQUARE TWO- FORMULA THAT READ: IF SQUARE ONE REACHES 5.00 OR OVER, THEN .50 CENTS IS ADDED ON.
    SQUARE THREE- WOULD READ THE AMOUNT BELOW 5.00 OR THE AMOUNT ABOVE 5.OO PLUS EXTRA 50. CENTS

    THANKS FOR THE HELP

    1. Hello Michael,

      You can use IF function to add a certain amount by your condition, e.g.:
      =IF(C5>=5,C5+0.5,C5)
      Here cell C5 is the amount you want to check. If it is more or equal to 5, the formula will show the value from cell C5 plus 0.50, otherwise it will show just the value from cell C5.
      As for square three, could you please describe the condition and the expected result in more detail?

  50. IN COLUMN J SOME CELL I ALREADY ADDED LIKE J54=SUM(J1:J53)AND J100=SUM(J60:J99)AND J130=SUM(J105:J129).LIKE THIS I ADDED IN K,M,O,Q.....ETC COLUMNS. NOW I ADDED THESE (J54,J100&J130) IN B350=J54+J100+J130.HOW TO PUT THE FORMULA FOR ADDING K,M,O,Q..ETC IN B351,B352,B353,B354..ETC

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)