Comments on: How to use Excel COUNTIFS and COUNTIF with multiple criteria

The tutorial explains how to use COUNTIFS and COUNTIF formulas with multiple criteria in Excel. You will find a number of examples for different data types – numbers, dates, text, wildcard characters, non-blank cells and more. Continue reading →

Comments page 55. Total comments: 2054

  1. Please Help.

    I have tree sets of data. Project Type, Resource Name and Days (columns Jan-14 to Dec-14).

    Project Type being.. T1, T2, T3, T4, T5.
    Resources X, Y, Z.....
    Resource Allocation Days against each month... 17, 19, 11, 20, 20 as values

    Now I need to calculate in a summary table.. Count of Months resources are allocated to a Project Type.

    Can you please help me.

  2. Hi Svetlana,

    I have been following your posts and find them very helpful.
    I just need help on something:

    I have a running list surgical procedures for 2014.
    Col A = date (running list)
    Col B = Types of Procedure (Procedure A, Procedure B,...)

    I want to count total number of procedures per month:
    January - How many Procedure A, B, C, D...?
    February - How many Procedure A, B, C, D... and so on.

    Thank you.

  3. I have following data sheets.

    sheet1data
    Branch Code Risk Number of high risk No of Risk Instances
    0101 H 04 18
    0102 M 11 20
    0103 H 9 24
    0104 L 5 13
    0105 M 10 26

    How I can fix formula that if branch code = 0101, check risk, count number of high risk and multiply number of risk instances

  4. Formula that will continuesly count based on Cell value and number it sequencially

    Col1
    ab
    cd
    ef
    ef
    cd
    ab

    Result
    ab-2014-01
    ab-2014-02
    cd-2014-01
    cd-2014-02
    ef-2014-01
    ef-2014-02

  5. HI there.

    I'm not well versed in COUNTIF, but I think this is what I need but not sure how to go about creating a formula.

    I have column with a $$ amounts in each cell and the corresponding column/cell would either be a Yes or No.

    I need to create a grand total of all Yes' and a separate grand total for No's.

    Can you help?

    Thanks in advance.
    Marcella

    1. =countif(A:A,"yes")
      =countif(A:A,"No")
      A:A which area you want to range selection data if you want.

  6. hi,

    i have a excel sheet where many of products like 20p,6P,2D
    and i want to sum of products in next sheet againts product when i filter any where in main product sheet

    1. Hello Chandan,

      I am sorry, I cannot follow you. If you can explain the task in more detail and provide a sample of your data, we'll try to help.

  7. Hi Svetlana, I've read most of these question and can't find one with the same problem as I'm having. I have a range, for example A3:A9, and I want a result when another cell contains text from one of the cells in the range. For example if A3="cat", I want a result when, for example, C2="a cat on a mat". If the situation was reversed, and I wanted to know if the contents of a cell was contained in a range, I'd do =COUNTIF(A3:A9,"*"&C2&"*"), but I want to know if any cell in a range contains text in a cell. My problem is I can't use add anything, like a wildcard, to a range, so I can't do =COUNTIF("*"&A3:A9&"*";C2)

    Is there a way to get this result?

    Thanks
    Ben

  8. I have a document where one column has list of name (around 100). Then I have so many other columns which can have any one value from set of 5 values(val1,val2, val3, val4, val5).
    Now I want to count number of val3 in row where name is Name12.
    Now to make is dynamic I have created 2 cells
    1. First to select Name (data validation = list)
    2. Second to select val (data validation = list)
    Now I want the next row to calculate total cell where value = value of second cell but from the row where name = value from first cell.

    1. Hello Amit,

      You need a VBA macro to fulfill this task. You can ask for it in special VBA sections on mrexcel.com or excelforum.com

  9. HI
    I PREPARED A WORKSHEET OF OUR EMPLOYEES TO CALCULATE SOME DATA SUCH AS THERE AGE AS ON DATE, DIVIDED THEIR AGE GROUPS ETC. BY GIVING DATE OF BIRTH. I CALCULATED THEIR AGE BY APPLYING =YEARFRAC(A1,TODAY(), 1) FORMULA. FURTHER I TRIED THEIR AGE BY DIFFERENT GROUPS SUCH AS 20-25, 26-30 ETC. FOR WHICH I APPLIED =COUNTIFS(C:C,"=CT",'G:G,">=21",G:G,"<=25"). BUT DID NOT SHOWING ACTUAL NUMBERS OF PERSONNEL COMES UNDER THAT CATEGORY IN SAID WORK SHEET. REQUEST HELP ME TO SOLVE THIS ISSUE.
    S/N RANK NAME DATE OF BIRTH AGE AGE 21-25

    1 CT MUNNA 18-06-1991 23 17
    2 CT MITHUN 28-02-1991 24
    3 CT BISWAJIT 26-06-1991 23
    4 CT KARTICK 14-10-1989 25
    5 CT SOURAV 22-10-1991 23
    6 CT VIKASH 28-02-1991 24
    7 CT VEERENDRA 03-01-1992 23
    8 CT OM PRAKASH 01-07-1989 25
    9 CT PAWAN 02-01-1990 25
    10 CT SANJAY 09-01-1990 25
    11 CT SUNIL 21-10-1990 24
    12 CT Atanu 10-07-1991 23
    13 CT KALI 05-02-1990 25
    14 CT RAKESH 05-01-1991 24
    15 CT SUNIL 09-02-1991 24
    16 CT PUPINDER 12-12-1991 23
    17 CT PATIL 23-11-1991 23
    18 CT Gulab 10-07-1991 23
    19 CT Bhupendra 18-12-1991 23

    ACTUALLY ALL ARE COMES UNDER 21-25 AGE GROUP BUT SHOWING ONLY 17 NOS.

    THANKING U

    1. Hi Satheesh,

      The correct formula is as follwos (F:F instead of G:G):
      =COUNTIFS(C:C,"=CT",F:F,">=21",F:F,"<=25")

  10. I am trying to count the number of occurrence of which the two certain conditions must be met. I tried it this way

    COUNTIFS('Brgy Cases,other Agencies'!$C$11:$C$13,"*Alangan*",'Brgy Cases,other Agencies'!$V$11:$V$13,"*Those Issued*") + COUNTIFS('Brgy Cases,other Agencies'!$C$15:$C$17,"*Alangan*",'Brgy Cases,other Agencies'!$V$15:$V$17,"*Those Issued*") + COUNTIFS('Brgy Cases,other Agencies'!$C$19:$C$21,"*Alangan*",'Brgy Cases,other Agencies'!$V$19:$V$21,"*Those Issued*") + COUNTIFS('Brgy Cases,other Agencies'!$C$23:$C$24,"*Alangan*",'Brgy Cases,other Agencies'!$V$23:$V$24,"*Those Issued*") + COUNTIFS('Brgy Cases,other Agencies'!$C$26:$C$27,"*Alangan*",'Brgy Cases,other Agencies'!$V$26:$V$27,"*Those Issued*")

    You can notice that there is 5 COUNTIFS function, and it's working fine. Unfortunately, when i am trying to add another set of countifs, the result returns a date format.

    Is it because there is a limit for summing up the occurrence in COUNTIF limited only to 5?

    Thank you.

    1. Hi Michael,

      A formula can contain up to 8192 characters, no matter how many COUNTIFS are used. Just change the format of the cell with the formula to Number.

  11. Is it possible to use the COUNTIF option when using fractions? I have a column of several lengths (32", 36", and 48"). I know the formula to count the cells - but I'd like to count the cells in the same column that only have 32" lengths in the cell. I tried the following and it did not work.
    =COUNTIF(A3:A25,32")
    =COUNTIF(A3:A25,32)
    =COUNTIF(A3:A25,"32")

    Thank you!!!!!

    1. Hi Becky,

      Try this formula:
      =COUNTIF(A3:A25,"32""")

      I know it looks very unusual, but it works : )

  12. Hello

    i need help to be able to use the countif function or similar to count text as 2 instead of just one is this possible

    thanks

    1. Hello Liam,

      Just multiply the COUNTIF result by 2, e.g. =COUNTIF(A:A,"text")*2 If you have any other conditions, please specify.

  13. Hi Svetlana
    I am trying to count the number of time a postcode is used within some 35,000 records. The postcodes have the format 'AB10 9BC' and I only wish to count the first group of figures. Using =COUNTIF(G:G,"AB10*")seems to work fine BUT.... Some of the first group of numbers consist only of three digits eg 'AB1 9BC'. This means that counting for AB1* also includes and counts AB10/AB11/AB12 etc etc. How can I count only AB1 postcodes in this example? Hope that makes sense and I look forward to your reply.

  14. Hi,

    I am trying to count the number of days marked "Holiday" in a set range but only those with a date less than 31/05/2014 but greater than 31/03/2014. The date range is in column R with the text fields in columns T:X.

    The other point is that the date column only has a week commencing date, not a date for every cell.

    I have tried =COUNTIFS($R:$R, "<="&I30,$T:$X, "Holiday")(where I30 is a date field with 31/05/2014). This produces an #VALUE! error. I haven't tried to enter the additional date criteria as I wanted to get the formula to work first.

    Taking each part in isolation (=COUNTIF($R:$R, "<="&I300 and =COUNTIF($T:$X, "Holiday")) works perfectly but not combined.

    Can anyone point me in the direction of where I'm going wrong?

    1. use formula in countifs best multiple condition apply one time

  15. i have a question, i am making a personnel planning for my employees. This is based on competences this means that specific people are trained to do job A but they can not do job B. I have 2 tables. One table "Training" with the name of the person and in the same row for which jobs they are trained so job A, job B, job D. Then i have the real personnel planning. I have on top which job needs to be executed so for example job A. Hence I put a name of the person. I want to have the cell highlighted in Red if the person is not trained for job A. I think to do it via COUNTIF but i am confused as I have different criteria, i need to refer first to job A, hence i need to search if that specific person is trained for job A. Can somebody help me please?

  16. Hi, for my case, cell A1 has an entry of 19/11/14 and cell B1 has an entry of 25/11/14. 25/11/14 is more than 19/11/14 by 6 days. I would like to set the formula for the dates that are more than 5 days to be false. Which means the entry above is false because it is 6 days.

    1. And for the entries that are less than or equal to 5 days to be True.

  17. I have only one column
    how to count how many row have time 7 am to 8 am and next 8 am to 9 am and next?

    7:31:11
    7:48:41
    7:51:35
    7:59:49
    8:02:01
    8:03:47
    8:06:41
    8:07:16
    8:07:47
    8:15:20
    8:19:43
    8:29:16
    8:33:42
    9:09:05
    9:09:36
    9:11:56
    9:19:27
    9:19:55
    9:20:25
    9:26:13
    9:34:33
    9:35:19
    9:47:05
    9:49:42
    10:10:10
    10:10:24
    10:10:37
    10:10:59
    10:11:10
    10:11:22
    10:11:43
    10:12:07
    10:12:29
    10:12:42
    10:13:04
    10:13:25
    10:14:05

  18. Dear All

    I need urgent help on one formula. For Eg

    A B C D E
    AXT-11232 2,500 89369 sent

    I want in column E the current date if any of the cells like A, B, C or D are filled. My data will be in the same format as in A, B, C. I just want the current DATE , whenever data has been entered in any of the cells marked as A, B , C or D

  19. I like to count First five character(number) as a individual number in a column but in different rows and same numbers count once only. How can I do this in excel? Fore example;

    A B
    98765_A1A2A3 ISO
    98765_A4 AATCC
    87654_A2A3 AATCC
    92345_A1A3 ISO

    1. =LEN(ABS(LEN(E3)+SUBSTITUTE(LEFT(E3,5),E3,1))-15)

  20. I like to count First five character as a individual number like; 98765_A3(98765),98765_A6(98765),87654_A2,(87654),92345_A1(92345) in a column but in different row and same numbers count once only. How can I do this?

  21. Hello,

    My countif formula returns 0 if it does not anything that matches the criteria. Is there a way to set up the formula so that if it doesn't find anything that matchs the formula returns "-" instead of 0.

    Thank you

  22. Hi! Here's the data I want to process. I want to count the number of entries a specific person submitted that matches anything except Not Qualified.

    DECLINED Peñalosa, Joyce Ann
    NOT QUALIFIED Calvo, Vivian
    CALLBACK Roda, Scepter John
    NOT QUALIFIED Henardino Jr., Edwin
    NOT QUALIFIED Asentado, Darell
    CALLBACK Peñalosa, Joyce Ann
    DECLINED Roda, Scepter John
    CALLBACK Alipio, Marisel
    DECLINED Emeterio, Absalon
    NOT QUALIFIED Solomon, Cinderella
    NOT QUALIFIED Solomon, Cinderella
    CALLBACK Toring, Jared
    CALLBACK Henardino Jr., Edwin
    NOT QUALIFIED Viscayno, Mabel
    NOT QUALIFIED Viscayno, Mabel
    DECLINED Solomon, Cinderella
    SCHEDULED Roda, Scepter John
    CALLBACK Emeterio, Absalon
    NOT QUALIFIED Henardino Jr., Edwin
    NOT QUALIFIED Trocio, Princess Joy
    CALLBACK Baruel, Coleen Grace
    CALLBACK Baruel, Coleen Grace
    CALLBACK Baruel, Coleen Grace
    NOT QUALIFIED Toring, Jared
    SCHEDULED Solomon, Cinderella
    SCHEDULED Gamboa, Estrella
    DECLINED Peñalosa, Joyce Ann
    DECLINED Calvo, Vivian
    NOT QUALIFIED Alipio, Marisel
    CALLBACK Baruel, Coleen Grace
    CALLBACK Arizobal, Argin
    DECLINED Calvo, Vivian
    NOT QUALIFIED Arizobal, Argin
    CALLBACK Toring, Jared
    DECLINED Emeterio, Absalon
    SCHEDULED Peñalosa, Joyce Ann
    NOT QUALIFIED Henardino Jr., Edwin

    1. Hello Shwan,

      Here you go:
      =COUNTIF($B$1:$B$100, "Person Name") - COUNTIFS($B$1:$B$100, "Person Name", $A$1:$A$100, "Not Qualified")

  23. I want to be able to check if data in one cell in column a = x and then if so count the data in adjacent cell in column b.

    To explain in more detail, I am creating a statistics chart where commissioners need to be able to be able to compare data for region 1 to region 2 and region 3.

    So column A will contain region keys such as 1, 1, 1, 2, 3, 3 then in column B will be ages. So if column A contains a 1 collect data in cell adjacent to the cell it is counting.

  24. Hi,

    I want to create a formula that counts how many of the letter 'y' occurs in particular columns but not in a range. The columns I want to search are D2, F2, H2, J2, L2, N2, P2, R2, T2, V2. The problem I'm encountering is that I don't want to search E2, G2, I2, ... I then want to have this formula for all rows in the spreadsheet. I don't see the answer above ... Thanks!

  25. Hi,

    Below is my issue. I have a few statuses which can be used to a closed ticket. I want to count all the closed tickets in the worksheet using the status. I used below formula but it did not work. Please help..

    COUNTIFS(A2:A1000,"A",B2:B1000,"Night",C2:C1000, OR("Solved","Cancelled",....))

    Department Shift Status
    A Night Solved
    A Night Cancelled

    Thanks in Advance!
    Chalinda

  26. Sorry it hasn't posted correctly - just looks a mess

    1. Hi Wayne,

      Regrettably, our blog engine is not perfect... If you can send us your sample workbook at support@ablebits.com, it will be easier for us to figure out the task.

  27. Hi, hope you can help. I'm trying to get a cell give me a figure of the following(Cell A is First Aid Course and Cell I says they attended or not attended) - I don't want it to count if the cell is blank (so basically a upcoming course)

    Example
    Course Title Start Date End Date Tutor Customer Name Attended?
    First Aid 01/11/14 14/11/14 John Paul Jones Attended
    First Aid 01/11/14 14/11/14 John Sarah Smith Attended
    First Aid 01/11/14 14/11/14 John Mo Ali FTA
    Food Safety 11/11/14 15/11/14 Lee Paul Jones Attended
    Food Safety 11/11/14 15/11/14 Lee Sharon Jones FTA
    First Aid 24/11/14 31/11/14 John Steve Bi
    First Aid 24/11/14 31/11/14 John Simon Gee

    Result to go into this type of box
    Course Name Total Referred Attended FTA
    First Aid ? ? ?
    Food Safety ? ? ?

    I can send on the sheet if its easier.

    Thanking you in advance

    regards
    wayne

  28. i need a formula to automatically check mark i.e(ü) mark a cell(eg:A1) if another cell (eg:H1)contains number:1
    Condition is H1 may contain any numbers like 1,2,5,6,8 to 20
    A1 should check mark only if there is 1
    ( if i drag formul A2 should check mark only if there is 2...and process upto A20 i.e number 20)

    Formula should come like this:
    (A1:A20) should Check H1 if perticular number contains in that cell i.e
    check H1 if it contains 1,3 then tickmark A1 A3
    check H1 if it contains 2, 20 then tickmark A2 A20
    check H1 if it contains 3 then tickmark A3
    upto 20numbers

    Formula should come like this:
    (B1:B20) should Check H2 if perticular number contains in that cell i.e
    check H2 if it contains 1,3 then tickmark B1 B3
    check H2 if it contains 15 then tickmark B15
    check H2 if it contains 18 then tickmark B18
    Upto 20 numbers

    IF IT IS NOT POSSIBLE TO USE NUMBERS IN SINGLE CELL
    I CAN TAKE H1 I1 J1...

    1. Hello Wilfy,

      If you can send your workbook with the source data and the result you want to get to support@ablebits.com, our support team will try to help you.

  29. If a worksheet arranged as mentioned below, HOW CAN COUNT HOW MANY CT BECOME BETWEEN AGE OF 21 TO 25. I HAD TRIED TO CALCULATE THIS IN ANOTHER SHEET BY USING FORMULA =COUNTIFS('NOMINAL ROLL '!A:A,"=CT",'NOMINAL ROLL '!C:C,">=21",'NOMINAL ROLL '!C:C,">=25"). BUT NOT SHOWING ACTUAL RESULT. REQUEST INTIMATE A SUITABLE FORMULA TO SOLVE THE THIS PROBLEM, PLEASE.
    NOMINAL ROLL (SHEET-1)
    A B C
    RANK NAME AGE
    CT Vinod Singh 22
    CT Rajesh Kumar Purohit 24
    CT VIJAY KUMAR 25
    SI CHANDAN KR PASWAN 25
    CT MANTU KUMAR RAM 23
    CT MITHILESH KUMAR JH 24
    CT PURUSHOTAM KUMAR 23
    CT AJAY MANDAL 27
    SI KAILASH SOREN 29
    CT DEBASHIS SHIL 27
    SI CHHOTU UJMODAK 30
    CT DIPAK TIGGA 27
    CT SUMANTA KR DAS 30
    CT SAGAR DHAMALA 28
    HC SANDEEP KUMAR 25
    CT RAYAZ AHMAD 23
    CT MONIRATH MONDAL 23
    CT BABLU NAIK 23
    HC PRADIP MONDAL 23
    CT TAPASA ROY 25

    1. Hi Satheesh,

      Your formula looks correct, just replace >=25 with <=25 :)

  30. Hi,
    How can I solved to multiple word in a criteria from single word in a excel file. pls help me

    1. Hello Rasel,

      I am sorry, your task is not clear. If you can describe it in more detail and give some example, we'll try to help.

      1. Thanks for your replay......
        Actually I would like to say,
        which formula should I use from multiple condition but value will be text.
        For example: sumifs functions.
        If I use to sumifs formula then it result any data but it not be text.....
        Please help me if you cooperated me..

  31. Hi,

    Column A: dates
    Column B: numbers

    I would like to calculate the number of times values in column B within a specified range occur within a specified date range. I've emailed an example file to support.
    Any help would be much appreciated.

    Many thanks,
    Dan

    1. Hi Dan,

      I believe the following formula will work for you:
      =COUNTIFS($B$2:$B$75, ">=6", $B$2:$B$75, "<=7", $A$2:$A$75, ">="&F2, $A$2:$A$75, "<="&G2)

      I emailed you the worksheet with the formulas a moment ago. Hopefully, this is what you are looking for.

  32. Hi, i have some formula populated column.

    I was trying to do a COUNTIFS(,"") to find the data which condition false.

    But i understood that COUNTIFS takes the formula as a value and gives a erratic value.

    Can you guide?

  33. Hi there - hoping you can help please!

    I have two columns

    Total Sessions Location

    1 S

    10 B

    5 S

    6 B

    what is a countifs formula to reference the location and return and count the sessions please?

    many thanks

    1. hi i am stuck in a prob.
      i want to count the two digits numbers in a given data.
      i applied =ccountif(RANGE,LEN(RANGE)>2)NOT WORKING

    2. Hi KC,

      If my understanding of your task is correct, you need a SUMIF formula rather than COUNTIF. For example, the following formula will pick all "b" locations in column B and return the total of corresponding sessions (column A):

      =SUMIF(B:B,"b",A:A)

      In your example, the result will be 16. If you are looking for something different, please clarify.

  34. I am trying to use countif to return a value of less than or equal to 15 for a range of cells in column A. It keeps returning a zero result. My formula looks like this : =countif(A3010:A3190,"<=15"). Does it matter that my A column is actually a formula that is resulting in the numbers being displayed?

  35. I have a set of customers and their ages in the Rows. In the Column section, I have a set of ranges from: <12months, <36months, <3years, 3 and <7years, 7 and <17 years, 17 and <40years. I want to add a "1" down the columns for each customer that is <12months, or less than 3 years, etc...Then I want to count how many of them are in that age group and get the total for each age range. May you help me please?

  36. Hi There,

    I have tried multiple functions and nothing works!! I have tried COUNTIF, COUNTIFS, SUMIF, SUMPRODUCT and I cannot get the result I need!

    can you help.

    I am trying to count the number of cells which contain a certain Text before a date.

    I used this formula to calculate all cells within a month that belong to date
    =SUMPRODUCT(--(MONTH(J6:K1000)=11)*(YEAR(J6:K1000)=2014))

    but now I need the first criteria to be a text specifically "DT*", and the second to be before november (<11)

    can you help?

  37. I have a workbook with multiple worksheets of inventory from various departments. I'm trying to create a summary page that sums the different categories of inventory from each worksheet tab. For example, how many desks do we have in total.

    My worksheets are set up like this:
    Desks. 26
    Chairs. 57
    Cabinets. 1

    But the info isn't in the same cells on each worksheet.

    Is this possible? Im p.yaong with countif but can't get it to work.

    1. Hi Dean,

      An immediate solution that comes to mind is using a combination of SUMIF functions like this:

      =SUMIF(Sheet2!A2:A7, "desks", Sheet2!B2:B7) + SUMIF(Sheet3!A6:A17, "desks", Sheet3!B6:B17)

      Where column A contains the categories of inventory and column B - the corresponding numbers. You can specify different ranges and different columns for each sheet.

      For more information about the SUMIF function with multiple criteria, please see this tutorial:
      https://www.ablebits.com/office-addins-blog/excel-sumifs-multiple-criteria/

  38. Nevermind! I found the solution elsewhere.

  39. I have a situation where the text that I want to count instances of is in various locations within the cells, e.g. A1 cell may contain 'Dog'; A2 cell may contain 'Cat, Dog', cell A3 may contain 'Bird, Cat, Dog'. I want to count all of the instances within the column that contains the word 'Dog'. It seems that no matter how I try to finesse this using COUNTIF and COUNTIFS with FIND and SEARCH etc. I can't seem to get this to work.

    Please help.

  40. I'm trying to create a spreadsheet to keep up with employee's absences, tardies, etc. for a year. Each incident is not counted after a year. So I have a cell on top that says today's date and used =today() so that when I open the file the date will fill in. I have the dates in the first column, the second is tardies, leave early and vacation. On top under today's date, I want it to show the total Tardies, for a year (from today minus one year).

    Today's Date '=today()

    Tardies ??? <---- This should be a total for the year using
    Leave Early ??? <---- same as above
    vacation ??? <---- same as above

    Date Tardies Leave Early Vacation
    11/01/13 1
    11/02/13 1
    11/03/13 1
    11/04/13 1
    11/05/13 1
    11/06/13 1

    For Example: the ???'s above should add from today (11/2/14) to 11/2/13 and give me a total of 2 so that when I open the spreadsheet on a certain day, that information will automatically update and show me how many tardies, leave earlies and vacation days they have for that given year.

  41. how would I write it sothat I could count a number if a condition is met EG
    A2=Knife
    B2=2
    A3=Spoon
    B3=4
    A4=Fork
    B4=3
    A5=Knife
    B6=7

    and I want b1 to count where A2:A5 = Knife but use the totals in B2:B6
    So it would count = 9

  42. Hi all,

    I Believe someone can help me here. what i want is from few different cell, i want to select certain letters or numbers. and want to to be able to link to one cell where it
    collect the selected letters and numbers from different cell and show as eg. cell 4 = (12345GB).

    eg. cell 1 = 0012
    eg. cell 2 = 4434
    eg. cell 3 = Good Buy

    i hope this is clear. its just so as i am inputting data on other cells and it generates like a ref if you like on the cell 4 as i instructed.

    please help?

  43. Hi,

    I want to count 1 column of data (contains £££) if another column contains a specific word.

    Could you help?

    Thanks

    1. Hi RC,

      I am not sure I understand your task correctly. Anyway, if you want to sum cell in column A if a corresponding cell in column B contains a given word, you can use the following SUMIF formula:

      =SUMIF(B:B,"word",A:A)

  44. 1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    25
    64
    54
    654
    546
    40

    so how can find highest number in excel sheet

    1. Hi Zaheer,

      You can use the MAX function for this, e.g:
      =MAX(A1:A20)

      Where A1 is the first and A20 is the last cell in the range.

  45. as example,,in the first sheet as below,,,
    A B D E F
    CODE FILLING STATION K.M AMOUNT ALAWNS
    5347 FALAJ AL-QABAIL F/S 15 16.74 1
    5294 FALAJ AL-QABAIL T 16 23.02 1
    5365 FALAJ AL-QABAIL N 17 23.02 1
    5727 SHINAS F/S 12 24.7 1.5
    5124 SOHAR BEACH F/S 25 24.7 1.5
    5530 SOHAR TREEF F/S 25 24.7 1.5
    5336 AL-WUQAIBA F/S 28 29.3 1.5
    5054 SOHAR SINAIYA F/S 29 29.3 2
    5299 SOHAR F/S 29 29.3 1.5
    5442 HUMAIRA F/S 30 29.3 1.5
    5211 AQAR F/S 45 38.51 2.5
    5438 AQAR TWIN FjS 47 41.86 2

    in the second sheet will be like as below,,,

    J k L M N
    CODE FILLING STATION K.M AMOUNT Allowances
    5727 ? ? ? ?
    5442 ? ? ? ?
    5294 ? ? ? ?

    My question is ,,if i type the number which is i column "A" in the secound sheet in the column "J" how can i make a formula to get the information from
    column "B" ,"C" , "D" , "E" in the secound sheet in the column "K" , "L"
    "M" ,"N" , ???or whatever columns in any sheet even at the same one,,

    i hope my question clear to you

    Thanks in Advance

  46. Hi

    I am currently struggling with this - I need a formula that works out the oldest date in A that does not have a processed date in Q.

    I have a formula that works out A =MIN(Audits!A7:A3026)but cannot figure out the COUNTIF for the second criteria, does anyone know if I am on the right track with a COUNTIF or am I just doing this wrong?

    Regards

  47. Thank you for the details shared.
    I have 4 digit numbers like 1234,2345,2334, etc. i want to know when 4 comes as 4th digit which number comes mostly in 3rd digit as well as in the 1st and 2nd.
    there are thousands of numbers looking forward for your reply. it would help me a lot

  48. hello

  49. -----A----------B------------C------------D-----------E
    --CODE------LOCATION-------KM-----------COST--------EXTRA
    --100-------SOHAR---------50------------25-----------2--

  50. hi,

    can you help please,,,
    i have five columns with differents informations and want to get all in another sheet by using only code, example:

    A B C D E
    CODE LOCATION KM COST EXTRA
    100 SOHAR 50 25 2

    the information in the cells never change,,,can i make a formula ,when typing a code then get the other information in another 4 cells

    thanks

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