Comments on: How to use Excel SUMIFS and SUMIF with multiple criteria

This tutorial explains the difference between the SUMIF and SUMIFS functions in terms of their syntax and usage, and provides a number of formula examples to sum values with multiple AND / OR criteria in Excel. Continue reading →

Comments page 14. Total comments: 675

  1. help me please now and today if possible.
    "i have to award DIVISION I, II, III, IV and 0 to my students who have scored different grades in their subjects either A, B, C, D and F where each carries different POINTS such as A=1, B=2,C=3, D=4 and F=5.And note that the minimum subject to be taken by a student is 7 and the maximum is 10 while others can take 8 or 9 is allowed. As well as award division is equivalent to the following points; for Div I =(1-7)points, Div II = (8-12)points, Div III = (13-24)points, Div IV = (25-33)points, Div 0 = (34-35)points. Note that the awarded Division to any students regards/considers to the first seven subject scored highest others less than 7 subjects awarded as INC - incomplete. Thus core subjects are 7 among the taken ones. plz help which formula or function am to apply to award for my class students now.
    thanks and be blessed abundantly by God. Amen.

  2. How do I get the total of all Fruit NOT sold by Mike?

    1. Hi Sheil,

      You can use a SUMIF formula similar to this:
      =SUMIF(B2:B10, "<>"&"mike", C2:C10)

      Where B is the column containing the names and C is the column to sum.

  3. Hi, I am trying to calculate a formula for the following data.

    t1 t2 t3 TOTAL
    -----------------------------
    10 10* 10* 20

    Critria:
    -In this I'm interested in sum of only * marks

    like in this total of 3 tests is 20 because there two stars of 10 marks.

    Can anyone please guide me?

  4. It is Amazing.

    I Love it

  5. Hi, I am trying to calculate a formula for the following data.

    Name Gender Grade
    ---- ------ -----
    ABC Male A+
    DEF Male A
    ACD Female A+
    ASD Male A
    SDF Female B
    AED Male A+
    LKJ Male A
    LOK Female B

    I would like to calculate Following things.

    1) Count of Male Students whose Grade is A+?
    2) Count of FeMale Students whose Grade is A?

    I couldn't able to write the excel formula to find the above things.

    Can anyone guide me?

  6. Hello,

    I am trying to come up with a formula that populates a sum of numbers from a different sheet (data source) - The tricky part is, I need the formula to cover a few different scenarios.

    Here are the things i need to cover:

    1. I need all of the items in a certain status (located in one column) that have a date range between 6/3/2016 - 6/9/2016 (located in another column)

    2. I need all of the items in 3 different status' (located in the same column) that have a date range between 6/3/2016 - 6/9/2016 (located in another column).

    The table I am trying to fill is on a different worksheet within the same workbook. I am having difficulty determining what formula set(s) to use to accomplish this. I will need to repeat the process weekly to have a running total - The data source will also be updated weekly, so these numbers need to be active, not static.

    Example:

    I need the total of all measures (items) that are in status "1-Template", status "2-Configuration", status "2.1-Reconfig" that were completed between 6/3 - 6/9 - I need this number to be populated into a table I created on sheet 2.

    Next, I need all of the measures (items) that are in status "6.3-Production" that were completed between 6/3 - 6/9- I need this number to be populated into a table I created on sheet 2.

    The range of the columns for status is A2:A5181
    The range of date completed is C2:C5181

    Any assistance is greatly appreciated! *I doubt anyone will see this immediately, but if possible, I need this today :) )

  7. 1 1
    4 2
    3 1
    2 2
    11 1
    1 2
    4 1
    3 2
    2 1
    11 2
    1 1
    4 2
    3 1
    2 2
    11 1
    1 2
    4 1
    3 2
    2 1
    11 2
    1 1
    4 2
    3 1
    2 2
    11 1
    1 2
    4 1
    3 2
    2 1
    Fist Colum count only 1, 11 number of count and second Colum 1 number of count please h

  8. Hi,

    I am trying to calculate a formula that encompasses more that one cell as a criteria(so I don't have to copy the formula 4 times which each cell.

    eg I have list of criteria
    DASHBORD
    A53 England
    A54 America
    A55 Scotland
    A56 Australia

    My formula currently is:
    =SUMIFS('Master List'!H:H,'Master List'!I:I,"Ticket",'Master List'!BN:BN,Dashboard!A53)

    How do I change Dashboard!A53 to include A54, A55 and A56 eg(A54:A56)

  9. We list our jobs on a WIP (work in progress) worksheet. Column E is the total revenue expected on the job. Column K is our actual cost in dollars of the job. Column P is the % of the job completed based on Cost/Revenue. Column U is the amount invoiced to date. Column X needs to tell us, based on the % complete, less previously invoiced amount, what our projected revenue will be. The part I can't seem to get is how do we tell Column X to only calculate up to 100% of Column P?

    If costs exceed 100%, it keeps calculating, telling us our projections need to invoice more then what the expected revenue will be. (Obviously we cant charge the customer more just because we didn't control our costs and keep them under 100%. I need it to only calculate up to 100%.

    E: Total Revenue for the job(we enter this amt)
    K: Job Costs in Dollar Amount (we enter this amt)
    P: Calculated % of Work Complete using the formula cost/revenue
    U: Amount invoiced to date (a formula calculating previously invoice + current invoicing)
    X: should tell us what our projected revenue is but only up to 100% in column P. Should column P go over 100%, we don't want to use the overage.

  10. I have a sheet that shows planned and actual delivered tonnage for a product.

    In the A column I have the delivery date, and in the B column, I have the month number ranging from 1 to 12 based on the date in the A column; in the I column, I have the planned tonnage, and in the J column, I have the actual delivered tonnage. The cells in the J column is blank if no delivery have been done yet.

    What I am looking for is a way to make a prognosis calculation based on what has actually been delivered so far this month plus the planned tonnage for the deliveries remaining this month.

    Right now, I have a hidden column that either shows the corresponding J cell value if it is bigger than 0 and if not, shows the I value. And then does a summation of this hidden column where the B value corresponds to the month number I am calculating.

    Is this possible using functions in Excel?

  11. Hi,

    Nice thread.

    In your example of =SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))

    my equivalent would be =SUM(SUMIF(C2:C9, {"2A","2B","2C"} , D2:D9))

    while 2 is determined dynamically within the same sheet say it is in cell E1 and then A, B and C have to be concatenated.

    I have tried something like below -
    =SUM(SUMIF(C2:C9, {"&E1A","&E1B","&E1C"} , D2:D9))

    or

    =SUM(SUMIF(C2:C9, {"&E1&"A"","&E1"&B"","&E1"&C""} , D2:D9))

    Can you please help?

  12. Is it possible to use sumifs to add numbers based on the letter and the date? For example: the sum of all A for 30-Apr (=6)

    29-Feb 31-Mar 30-Apr 31-May
    A 1 2 3 4
    B 1 2 3 4
    C 1 2 3 4
    A 1 2 3 4
    E 1 2 3 4
    C 1 2 3 4
    G 1 2 3 4
    H 1 2 3 4

  13. This is a great article with a bunch of new tricks I do not know about. My question is that when I used the array argument in a SUMIFS function, the results are calculated from only applying the first argument in the list, and ignored the rest. In your example, when I used {1, 2, 3} as the array argument, the SUMIFS function only returns the calculation based on 1, not including 2 or 3. My excel is from Microsoft Office Professional Plus 2013. When I tried to search "array argument" in Excel Help (clicking the "?" on the upper right corner, there was no relevant entry appeared.

    Does this array argument a newer feature than Office 2013 or an older feature but eliminated from Office 2013?

    Thanks for your help!

  14. If I want to add up the total spent in a budget heading (a drop down list) for a particular month across sheets why is:
    =SUMIFS(Sheet2!D2:D11,Sheet2!A2:A11,"Marketing",Sheet2!C2:C11,"Sep") not working when D is amount spent, A is budget heading and C is date

    Thank you

  15. Hello. I've tried to use "sumifs" and "sumproduct" functions, but
    seems doing something.

    Spreedsheet 1: Column A= dates of one month, column B=list of products on each day of a given month, column C=qty;

    Spreedsheet 2: Column A=dates of one month, Column B = need to get sum of a particular items from sheet1 column B, of course taking in account date of selling.

    Thank you very much in advance.

  16. Hello,

    SUMIF or SUMIFS function add the values specified in 'sum_range'. Usually 'sum_range' should be a row or column of values.

    What if the 'sum_range' is a table of array. I mean if quantity of fruits supplied every day is tabled, I want to add quantity of apples supplied in a calendar week, for example CW18. Here 'sum_range' would be "C1:I9". Will It possible to have simple syntax for such summation?

  17. Hi, I have a formula

    SUM(SUMIFS('File Name'!D:D,'File Name'!$C:$C,{131,132,133,138}))

    that works beautifully when the source file is open but gives me the #VALUE error when it is closed.

    Is there a way to rewrite it so that I use only the SUM and IF functions, hopefully avoiding the error?

  18. thanks dears,
    could you please help me with multiple (or) conditions in multiple columns
    i.g:
    SUMIFS(A1:A10,B1:B10,{"Mike","John"},C1:C10,{"Apple","Orange"},D1:D10,"",...)
    is this can work in somehow, I try but its give unreal result (Less than the actual) "it seems like the excel is calculating the second (or) condition based on the filtered data in the first (or) condition.
    thanks in advanced :),,

  19. hi,
    i want to ask
    how about if we use criteria (A1:G1)

  20. Hello,
    i want to learn below farmula, kindly teach me in detail and example.

    SUM(IF(logical_test,[value_if_true],[value_if_false])

  21. Thanks - was struggling with SUMIF and AND together - this explained it clearly and concisely and my report now does what I want it to do.

  22. I want to sum a range to be identified (ex: I have multiple case Id's in which I need to sum the age of the same case id's). Any find formula available.

    Example
    Case ID Hold Age
    10241993 1342.87
    10241993 0.02
    10241993 126.38
    10241993 0.01
    10505177 144.04
    10505177 18.83
    10505177 117.7

  23. ... might be nice to point out that the ranges cannot be in adjacent worksheets within a workbook, but must be on the same sheet as the cell in which the formula is entered. I've been trying for an hour to find out what's wrong with my SUMIFS using the sum range and one of the two criteria ranges on another worksheet in the workbook. Always got a 0. Then I moved it all to the same worksheet and it worked fine. Waste of an hour, though...

    1. Hi Dave,

      In fact, the sum range and criteria ranges can be on different worksheets provided that all the ranges are of the same size. I've just tested a simple formula like =SUMIFS(Sheet1!A1:A4, Sheet2!A1:A4, "a", Sheet3!A1:A4, "b") and it worked just fine.

  24. I am trying to obtain a year to date utility total using this sumifs formula.
    =SUMIFS(cost,meter,$B7,service,$C7,period,"<=$M$1",year,$I$6)
    Where range names are
    cost = total invoice
    meter = the meter number
    service = type of utility (gas, water, electricity)
    period = the accounting month January - December
    year = 2015 or 2016

    Thus, the report contains data for all periods 1 - 12 but I want a year to date for any month end (e.g. March would include periods 1, 2, and 3).

    Any ideas would be appreciated.

    Thanks

  25. I managed to find a solution to my earlier query. I used the sumproduct function and it works perfectly. Irrespective of this, I'd still be interested to know why the sumifs function gives out an error. Regards Charles

  26. I'm trying to use the sumifs function in the last column of a Table (Table1) with a Header Title "Total". Want I need is to add cells of each row based on part of their header title. The first header title within Table1 is "Country", while the other header titles include an abbrev. of the name of each month in the year (i.e. Jan, Feb, etc.). For instance, the header title of B2 is "No of Ships - Jan", C2 is "No of Ships - Feb" and so on until - Dec. The formula is in the rightmost row with a Header Title = "Total". I want to add the No of Ships from x month to y month (x & y being two numeric values inserted by a user outside of the table. Therefore I first started out by using the following formula: =month(datevalue(right(Table1[[#Headers],[No of Ships - Jan]:[No of Ships - Dec]],3)&"1")) which works fine when pressing F9 as the resulting value = 1,2,3..12. However when I try to incorporate the array into the sumifs function, I get an error. Here's the first part of the formula placed in n3:
    =sumifs(b3:m3,month(datevalue(right(Table1[[#Headers],[No of Ships - Jan]:[No of Ships]],3)&"1")),">="&A1),... A1 = 5 (meaning start adding from May). If I can get the first part to work, I would then continue the sumifs with the same criteria but based on B1 value (say = 8) being the last month to take into account (in other words add those cells from May to August only). I'd appreciate your help and guidance. (Note that the above is only a simplified version of Table1, which actually incorporates other Header Titles but do not include the month name). Thanks Charles

  27. DATE A B C
    01-01-16 999.9 2548.75 2510
    01-01-16 1982.7 522.15 0
    01-01-16 440.9 0 54
    01-01-16 535.5 294.95 0
    02-01-16 274.4 0 19
    02-01-16 697.5 313.5 0
    02-01-16 690.3 179.25 120
    03-01-16 158.16 153.8 0
    03-01-16 207.5 0 560
    04-01-16 1034.1 354.2 0
    04-01-16 0 1165.5 8600
    04-01-16 180.3 27 0
    04-01-16 329.4 665.1 0

    Please any one can help me.. i want to total date wise of "A" , "B" AND "C".
    Please suggest me how can i total this data..

    1. Hi MANOJ KUMAR,

      You should use the following array formulas to sum the values for date 01-01-16:

      to sum the "A":
      {=SUM(B1:B10 * (A1:A10 = DATE(2016, 1, 1)))}
      to sum the "B":
      {=SUM(C1:C10 * (A1:A10 = DATE(2016, 1, 1)))}
      to sum the "C":
      {=SUM(D1:D10 * (A1:A10 = DATE(2016, 1, 1)))}

      where

      "DATE" values are in A1:A10
      "A" values are in B1:B10
      "B" values are in C1:C10
      "C" values are in D1:D10

      To enter this formula press CTRL+SHIFT+ENTER.

  28. I think the SUMIF is exactly what I need - but, I want to find matches in column 1 and 2 (names for example) and then when there is a match - check the third column for a value and place the sum of the values in the third column. If somebody can help I would SO appreciate it..

    1. Hi Beth,

      You can use the array formula to solve this task.
      For example:
      {=SUM(C2:C10 * (A2:A10 = "Text1") * (B2:B10 = "Text2"))}
      where
      The values are in C2:C10.
      The column 1 names are in A2:A10.
      The column 2 names are in B2:B10.
      To enter this formula press CTRL+SHIFT+ENTER.

  29. Hi Svetlana

    Column A: Project ref
    Column B: Total hours on Project in one day
    Column C: Total hours on one project overall
    Rows = Days of the week

    Column A consists of multiple reference numbers which repeat if a project is worked on for more than one day, but not necessarily on consecutive days. I want to be able to cumulatively add the total hours worked on a project over multiple days using a sumif calculation. For example i would like the total number of hours worked on project ref 222 over several non consecutive days in one month. The project ref range is large and therefore i do not want to specify "222" in the criteria but all instances where a reference number is repeated in the ref column.

    Can you please help.

    Thanks

    1. Hi Petros,

      You should use the following array formula:
      {=SUM(B2:B10 * (A2:A10 = "222"))}
      where
      "Total hours on Project in one day" is in B2:B10
      "Project ref" is in A2:A10
      To enter this formula press CTRL+SHIFT+ENTER.

  30. Thanks you all.

  31. hello,

    Please send me Formulas

    Exp.
    this is pan number list

    AAAPA1234A

    AAACA1234A

    AAAHA1234A

    When Pan number Four letter is P=2
    otherwise=1

    AAAPA1234A 1

    AAACA1234A 2

    AAAHA1234A 2

    AAAFA1234A 2
    ABCPA1250A 1

    Please send me formula....

  32. Hello,

    I am trying to say sum a column which does not equal a range of criteria in one criteria range.

    This is an example of my criteria range & criteria, but I cannot get it to work: (The actual formula is more complex and much longer)

    Data!$A:$A,""I have to put SUM() around the SUMIFS() which works, but not when I add ""&

    Any suggestions?

    Thanks

  33. I want to Use SUMIF Function in a column where i need to find different values and total it for example:

    i want to use sumif formulae like this in n. no of cell

    SUMIF(I1:I1000, "6625", K1:K1000)
    SUMIF(I1:I1000, "6626", K1:K1000)
    SUMIF(I1:I1000, "6627", K1:K1000)
    SUMIF(I1:I1000, "6628", K1:K1000)

    now how can i rite this in entire cell with different values to search

  34. Dear All

    Looking for needy help..

    I have to prepare one Status report to My Manager, Actually in my office several consultant working on a daily basis and my boss given to them number of works on a daily basis for example:- A, B , C consultants, Boss has given number of works to them like For A on 21-01-2016 hes has to complete 10jobs and "B" 5Jobs,C" 8. On 22-01-2016 "A" 5,"B"8,"C"10. We want to know how much they completed as on date and how much pending namewsie.

    Waiting for positive reply

    Thanking you

  35. Hi Alexander,

    Please check the below table for the same.

    OrderID AgentName Center Qty Sum of Single cell Qty
    VI8736656 Amit Kumar Techm Noida 23 5
    VI8736685 Rakesh Chand Techm Noida 245 11
    VI8736986 Ruby Techm Chennai 23 5
    VI8737133 Lokesh Kushwaha Techm Noida 31 4
    VI8737537 Seethala Devi L Techm Chennai 234 9
    VI8737539 Seethala Devi L Techm Chennai 2 2
    VI8737682 Md Rafee Alam Techm Noida 134 8
    VI8738184 Komal Singh Techm Noida 246 12
    VI8738222 Chakshu Jain Techm Noida 309 12
    VI8738396 Md Rafee Alam Techm Noida 278 17
    VI8738507 Aanchal Techm Noida 245 11

  36. Hi Guys

    Anybody could you help me and can you explain the below formula step by step
    Because I am not able to undersand this formula.

    please please help me

    =SUMPRODUCT(1*MID(H7,ROW(INDIRECT("1:"&LEN(H7))),1))

  37. Hi!
    I'm trying to use function SUMIFS to sum up numbers by account (column A), product (column B), year (row 1) and scenario (row 2). But the only thing I get is #VALUE.
    Is it possible to at all to use SUMIFS in such cases? Or is there any better function for this purpose?

      1. Hi Irina!

        Thank you! I think I have figured out it in a slightly different way:-) But I'll keep in mind your site!

        /Melissa

  38. I almost have this figured out...Help please.
    I have a simple list of ages and zip codes. Ages are broken into categories as such:0-5, 6-11, 12-18, Adult, Adult +. The zip codes and ages need to be tallied. I need to know how many of each age participated and I need to know from what zip codes these folks have come from. Here is an example...
    A B C D E F G H
    55 ADULT 97478 0 0 0 0 0
    56 ADULT 97402 0 0 0 0 0
    57 3 97402 1 0 0 0 0
    58 3 97402 1 0 0 0 0
    59 6 97402 0 1 0 0 0

    AGE 0-5 6-11 12-18 Adult Adult +
    TOTALS

    ZIP CODE 97402 97404 97405 97478
    TOTALS

    The formula in D57 is =IF(AND(B60>0,B60<6),"1","0") This formula does exactly what I ask it to do. However, I am unable to sum each column, The formula does not recognize text, and I am not sure what to do with The zip codes. Can you help?

    1. Hi Shelly,

      You can use the COUNTIF or COUNTIFS functions, please see the following articles for more details:
      COUNTIF in Excel
      How to use COUNTIFS

      If you need to get a unique list of ZIP codes for each age group, then please follow these steps:
      - Create a copy of your worksheet;
      - Add a helper column J, enter the following formula into its first cell:
      =IF(AND($B1>=0,$B1<=5),"0-6",IF(AND($B1>=6,$B1<=11),"6-11",IF(AND($B1>=12,$B1<=18),"12-18",IF($B1="Adult","Adult","Adult+"))))
      - Copy it down the column;
      - Run our Combine Rows Wizard add-in
      - Use column G as the key column, choose to combine the unique values in the column with the ZIP codes and separate them with a comma.

  39. I have a budget workbook I'm working on.
    We have expenses assigned to Categories, Types. They are allocated in Programs. In one worksheet, columns have an array formula to sum up data from an expense page

    {=SUM(SUMIFS('Dollar Data'!$E$2:$E$352,'Dollar Data'!$A$2:$A$352,$A10,'Dollar Data'!$C$2:$C$352,D$154:D$173))}

    Dollar Data A is Type,
    Dollar Data C is Category,

    Categories are allocated to Programs (multiple columns).
    Right now all of a Category is assigned to a Program, and we want to assign it proportionally. Is it possible to tweak the array formula to use a percentage? We could specify it next to our Category Criteria in D$154:D$173, e.g. E$154:E$173.

    as a simplified example:
    e.g. Program A = Break Room Program B = Parties.
    Program A Prcnt Program B Prcnt
    Silverware 60 Silverware 40

    (Type is Knives, Forks, Spoons, all added together into Silverware by the array formula).

  40. Excellent, many thanks for your help, the community appreciates !

  41. I'm trying this formula but its not working on my sheet
    =SUMIFS(I10:I88,G10:G88,"AGO - PETROL",H10:H88,"874")
    My table has all the columns but when pressing enter key to get the
    result comes a #value . Can anyone help me knowing the problem?

    1. Control + Shift + Enter instead of Enter to enter it as an array formula...

  42. Very helpful article, and it got me almost all the way to a solution, but now I am stuck because I can't get the SUMIFS function to apply as an array formula. Right now I am using the following (=SUMIFS($J2:$J152, $L2:$L152, L2, $E2:$E152, E2). I need the values with the $ to stay static and for the values without the $ to move as the formula is applied, but with SUMIFS it doesn't seem to work the way normal formulas work. How can I get SUMIFS to respect the $?!

    Thank you.

    1. I believe you would not include the parentheses around the very outside around the =. you would enter the formula, and hit Control + Shift + Enter to create the array formula...

  43. Hi,

    Sorry if I missed this in your post. I am using the below formula:

    =SUM(IF(FREQUENCY(A2:A367,A2:A367)>0,1))+SUM(IF(FREQUENCY('Sheet2'!A2:A367,'Sheet2'!A2:A367)>0,1))

    This gives me a total unique count from two separate sheets. However I now want to count the unique values with criteria attached. I tried the following:

    =SUMIFS(IF(FREQUENCY(A2:A367,A2:A367)>0,1),B2:B4367,">="&Settings!B6, B2:B367,"0,1),Sheet2!B2:B367,">="&Settings!B6,Sheet2!B2:B367,"<="&Settings!B5)

    However it only comes up with a #Value! error. I feel as though I am missing something obvious with the structure of the nested IF within the SUMIF but I can't see it.

    Thanks for any help :)

    1. Just as an update, I attempted nesting the IF functions against the criteria and against the range manually but I was still unable to retrieve a functional result.

      1. Hello, Michael,

        To find an error in the second formula we need to see your data. You can send us a small sample table with your data in Excel to support@ablebits.com. Please include the link to this article and the number of your comment.

        1. Thank you for your reply. I worked it out as follows:

          =SUM(IF(FREQUENCY(IF(CUKD>=Settings!B8,IF(CUKT"",MATCH("~"&CUKT,CUKT&"",0))),ROW(CUKT)-ROW('Current Year UK'!A2)+1),1))+SUM(IF(FREQUENCY(IF(CUSD>=Settings!B8,IF(CUST"",MATCH("~"&CUST,CUST&"",0))),ROW(CUST)-ROW('Current Year US'!A2)+1),1))

          The structure was flawed in that the criteria was being applied but not as part of the SUMIF function, at least not correctly. It was far easier instead to simply specify nested IF functions as above and push as an Array. The named ranges are simply just to reduce loading time for adjusted ranges, it will eventually become a requirement to use SQL to perform the same function, but by that point I think I will migrate from Excel altogether.

  44. After much "Googling", this is the best treatment of this subject that I saw.

    Mega-thanks to you Svetlana.

  45. I want to use the sumifs formula but for a "text" in the sum_range. Is this possible? I have a list of products or tours, by date with a particular guide or guides. I am using sumifs to get the criteria of the date and guide name but the resulting answer is in text (product), not a numerical value. What would be the formula for sumifs for a text value?

    Thanks in advance!

    1. Hello, Robin,

      It's not possible to sum text with SUMIF. The cells must contain numbers, names, arrays, or links to numbers. Empty cells and text cells are skipped.

  46. Hi There are subject codes and grades scattered in various columns & rows in excel sheet . how can i extract counted grades & code in one cell of excel sheet jointly. FOR EXAMPLE CODE 30 GRADE A1 I WANT TO KNOW 4 Columns is there for formula help automatically count and write whole sheet how many code 30 get A1 grade manually count code 30 grade A1 is 4.please solve my problem.
    chart is given below.

    NAME CODE GRD CODE GRD CODE GRD CODE GRD
    RAM 30 A1 41 A1 30 A1 41 A1
    SHYAM 41 A1 30 A1 41 A1 30 A1

    Advance thanks

  47. As soon as possible please give reply

  48. Hi, There are subject codes and grades scattered in various columns & rows in excel sheet. how can i extract counted grades & codes in one cell of excel sheet. For example code 30 grade A1 how to count how many 30 code get A1 grade manually count code 30 A1 grade is 4. but i want to formula help automatically count and write.Please help me. pandey5375@gmail.com
    chart is given below.

    NAME CODE GRD CODE GRD CODE GRD CODE GRD
    RAM 30 A1 41 A1 30 A1 41 A1
    SHYAM 41 A1 30 A1 41 A1 30 A1

  49. Hi,
    in my spreadsheet a column has date values. eg
    F20 = 1/1/2016, G20 = 120
    F21 = 1/2/2016, G21 = 80
    F22 = 1/5/2016, G22 = 120
    F23 = 1/6/2016, G23 = 100
    F24 = 1/9/2016, G24 = 200
    and it has been entered so on.

    I'm querying F column for the cell having latest date value. here F24 has.
    then i want to retrieve cell G24 value in corresponding row, here G24 = 200 and want to store in Cell E2.
    Now as i enter new data in F25:G25, F26:G26 and so on Cell E2 should update automatically as per latest date entry in F column.

    please guide here how to achieve this in Excel 2013.

    1. Try using max(F:F) as your criterion.

  50. can sombody tell me

    how is use this
    =sumproduct(K1:K10)*(L1:L10)*(O1:O10)*(P1:P10)*(B1:B10={"A","B","C")*(1)

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 :)