1. airforceone

    countifs not working in macro

    Sorry but I'm back again.... I hope everyone is well and good... anyway I have 2 Sheet (Dummy and Updated) from Sheet Dummy my code would supposedly insert my Sum and CountIfs formula in the designated cell in my Sheet Updated, although it runs but the problem is the total value does not...
  2. F

    Count (countifs) norm exceedances with variable dates and variable norms formula or VBA

    Hey everyone, I have substance measurement data from water quality checks. The data is listed in a table starting with the date (dd/mm/yyyy). Above the table are the norms. The variable norms are in green. Those norms are generated on a second tab, and change (sort of) weekly. I need to...
  3. S

    TRUNC function in COUNTIF or COUNTIFS produce error message

    Hello, Would you know why the usage of a TRUNC function into COUNTIF our COUNTIFS, as here-after, produce the following error message ? =COUNTIF(TRUNC($A$2:$A$6),D$1) The range $A$2:$A$6 contains some numbers with decimals and I aim to count how many of them matches the cell D1 that contains...
  4. J

    Count no. of instance of value appearing based on date in a tab where the column position varies in this tab

    Hi I am looking for some help. I am looking to count the number of passed test cases per day which I currently use COUNTIFS formula. =COUNTIFS(Runs!$I:$I,C2,Runs!$E:$E,"Passed") where 'Runs' is the data source tab and row 2 has the date value. However my data source (contents of Runs tab) has...
  5. B

    =IF(COUNTIF for one range and multiple criteria

    Hi all! I am trying to convert numbers in multiple columns to a value in words. I am using the =IF(COUNTIF(range,criteria),"value if true","value if false") function and able to make it work with only one criteria, but having trouble returning a value when using multiple criteria. This is what I...
  6. T

    COUNTIFS Array of different sizes

    Hi, I am trying to use countifs with two sets of criteria. The second argument needs to count if any values in a list (Sheet1!I1:I10) matches another list (Sheet2!A1:A3), where as the first argument just counts if a range (K1:K10) matches a single value (A2)...
  7. T

    Check if row values in column cannot be found in another range

    Hi all, I would like to: Check if row values in Column AP cannot be found in another workbook's Range("D2:D31"), return "NULL" in Column AO Else, return row values in corresponding row of Column AN in Column AO I'm not sure how to create a formula which would find row values which do not...
  8. T

    If Multiple Condition and Nested CountIf Formula Not Working

    Hi all, I would like to: Check if row value in Column AM = "APAC" Check if row value in Column AR = "APAC" Check if row value in Column AJ has partial string match containing "OFIA" Check if row value in Column AJ has partial string match containing "Intra-Asia" If all above...
  9. R

    Filtered COUNTIFS

    Hi everyone, I have this formula: =COUNTIFS(Y4:Y61,">=-10.0",Y4:Y61,"<=1.9")/COUNTIFS(A4:A61,"*") Which gives me a percentage. But it does not work if I use a filter. Am I doing something wrong?
  10. G

    reference cell even the row is deleted

    i have no clue to doing this man, i expect the output is to be the right chart, but on the left chart is what i’ve done i want the cell to still remain the same number even the row is deleted and if i add new no. it still going +1 i need your help guys
  11. S

    Countifs with offset returning #value

    Hoping somebody can help out...please :) I have a sheet that I want to do countifs on but when I put 2 working countifs together in one formula I get #value? 1. =COUNTIFS(Colleagues!$E:$E,$B$4) - returns the correct result 2...
  12. V

    COUNTIFS with multiple OR conditions

    Hi there, I hope you can help me as I have been struggling with this issue for weeks. I am handling data for an insurance provider package supplier company. One of our company's providers in particular is complicated as there are several possible outcomes of a customers' journey. I have been...
  13. H

    Help with COUNTIFS

    Hi, I am trying to COUNTIFS function with a few different conditions: =countifs('Key Property Info'!A:A,"media",'Key Property Info'!H:H,{"contract sent","apps sent"}) I would like excel to return the value of the the contract sent and apps sent figures, where they are applicable to 'media'...
  14. M

    Multiple INDIRECT within a COUNTIFS

    Hello and thank you for looking at this. I have just started using INDIRECT and am finding it very challenging. I have a couple of use cases. One I have solved, this new one is killing me on syntax. I am using a dropdown to change a year. When it changes my formula updates to reference a new...
  15. L

    Count Values in 2 Columns return unique for row.

    Hi, Thanks in advance for any assistance. Trying to count "*"&YTD!A15&"*" In Columns A:A and L:L. This formula counts both and returns the value 2 if both columns contain that string. The problem is in the records it could be either in A:A OR L:L OR Both. In the case that it is both, how...
  16. E

    COUNTIFS with Multiple Fields

    I am currently writing up a spreadsheet for my football team to count each players games played, goals, assists, clean sheets and MOTM. I am doing this for each game and have a separate columns for each games goals, etc, as you can see in the screenshot. I am trying to do a countifs formula on...
  17. S

    "countifs" across multiple sheets

    This works for the sheet it is on =COUNTIFS(H:H,"PFIZER",I:I,">=1/1/20") need a total count from all my sheets. I can get it to count one value but not if i need it to look at two. sheets are listed in column A. A1:A52
  18. M

    Count values with reference from a cell on a different worksheet

    I'm trying to count the number of shifts of employees by looking up by employee. The shift data is on a seperate worksheet. I would like to use a formula that refers to the cell for each employee and then finds the corresponding name and counts the number of shifts in the rows and columns on...
  19. E

    COUNTIFS doesn't work but IF works just fine

    I have 3 cells with numbers E20 is 5, F20 is 2 and Y20 is 3. In cell Z20: =IF(Y20>F20,IF(Y20<E20,1,"")) gives me 1 However, =COUNTIFS(Y20,">F20",Y20,"<E20") gives me 0 I can't figure out what happened. I have checked using isnumber() that all values are in fact numbers. I need to use...
  20. T


    Hello, I am trying to use COUNTIFS and INDIRECT but my formula is not working. I have different sheets, and each sheet represents a store location. In each sheet, there is a Sales Person name and the sales amount generated for each store item (e.g., shoes, tops, dresses). In my summary sheet...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back