# countifs

1. ### Combining countifs

Hi, Help needed please. I need to combine two Countifs statements with an ‘or’ but don’t know how to do it. I’m not an excel expert as you’ll soon see. I want to be able to look for B1’s and if either the value in Entry1 or Entry2 is greater than zero, count them. However, if they’re both...
2. ### >= error

Hello. =COUNTIFS(\$AR\$13:\$AR\$1000,"W",\$AP\$13:\$AP\$1000,">=2") gives me correct result. if i want the 2 value to be represented in a cell so i can change it I get incorrect results. here is what im typing. =COUNTIFS(\$AR\$13:\$AR\$1000,"W",\$AP\$13:\$AP\$1000,">=\$A\$1") where A1= 2 my value should be...
3. ### Formula for COUNTIFS Exact Match in Range of Multiple Columns

Hello. I'm trying to find appropriate formula to count exact text in multiple columns where the criteria is the date range (between 2 dates). I have tried with =COUNTIFS(A2:A25,A32,C1:X25,">="&B30,C1:X25,"<="&B31) and the result is #VALUE! . It is counting fine if the criteria range is in one...
4. ### Countifs in DAX

Hi Guys, I have some problems with counting ID's that meet 2 conditions but on a different row. Can someone help me out with a formula? See the table below for example. Thanks a lot! ID ID Condition 1 Condition 2 If ID meets condition 1 and 2 then return ID, else return blank 1001...
5. ### COUNTIFS or SUMIFS

Hi, below is a partial formula. How can I count the number of AND criteria's that are met? (AND(CFW\$16=2,CFW48=1),AND(CUI\$16=2,CUI48=1),AND(DIU\$16=2,DIU48=1)) I just need a 1 for one AND criteria, 2 for two AND criteria's, and 3 for three AND criteria's. Thank you, David
6. ### Countifs, Sumproduct...?

Hi, Sorry if there is another thread about this, but I can't find any, or I don't know the right search criteria :-) Please see below table. Do you know a formula to calculate how many times two specific companies, has been working on the same project? In below example the results should be...
7. ### COUNTIFS and SUMIFS Help

I am attempting to complete SUMIFS and COUNTIFS and am not getting any results. A sample formula I am using is: =COUNTIFS('Tracking Sheet'!\$H:\$H,"2016",'Tracking Sheet'!\$D:\$D,"Repaid",'Tracking Sheet'!\$D:\$D,"Closed") If I break the formula apart and only have two arguments, I get a result. I...
8. ### #VALUE error for COUNTIFS formula

I am getting #VALUE error the below COUNTIFS formula, tried various possible changes based on research, but nothing works =COUNTIFS(\$G\$2:\$G\$2184,02.11.2019,\$I\$1:\$I\$2184,Resolved) Both conditions work when used independently
9. ### Countifs where date is less than

Hi I'm struggling with getting a formula that will do the above to work. i was actually trying to obtain a count where a date is older than 8 years ago but could not get that to work. So I then tried entering that formula (=DATE(YEAR(TODAY())-8,MONTH(TODAY()),DAY(TODAY()))) in to it's own...
10. ### Exact matches when using COUNTIFS

Hi there, I am trying to use the COUNTIFS funtion to highlight duplicate rows. Here is an example of my data: _____A _________B _____C_______D 1 UserID _____Name __Licence__Duplicate? 2 ABC1 ..............Joe ........1CL** ......=COUNTIFS(A:A,\$A2,C:C,\$C2)...
11. ### Greater than / less than when in a table.

So, I'm pretty much a noob at Excel and this will probably be one of those kinds of questions, but here goes. I have a table with the first two columns being 1 hour increment markers (staggered +1hr), labeled T1 and T2. the next column will be a set of COUNTIFS functions asking if a set of...
12. ### Countif Month for Attendance Sheet

I am making an attendance sheet to count the number of times a person is present in a certain month. (url to photo of sheet attached) Column A is Dates, and Columns C-D lists "yes" if present on that date and "no" if not present on that date. Column G lists months (Feb, Mar, April..), and in...
13. ### Conditional formating with a formula

I have a spreadsheet of 1400 students and I'm trying to find which ones are eligible for our program. I have adjusted gross income in one column and dependents in another column. I have a countifs that accurately tells me how how many are eligible. But I can't figure out how to highlight the...
14. ### CountIfs?

I have a column (F) that contains the Qty order for each customer I need a forumula that looks down column F and counts how many orders I had where the quantity was between 0-49 (Greater than 0 and less than 50)
15. ### countifs and also unique value

Hello all I am trying to use a countifs statement to count the number of lines that match a date but i want to also include only unique lines. [=COUNTIFS(Data!Q:Q,C6)] This is the countifs statement in cell "E6" I also want to add the unique count to this formula which is in [Data!V:V] which...
16. ### Counting instances in workbook with both AND and OR statements

All, I have a workbook that contains site names, technology at each site, then multiple columns where my KT teams can enter times for conducting knowledge transfer. Column A - Site Name; typically have 4-6 rows containing the Site name Column B - Technology; in rows for each site, there are...
17. ### If & Countifs

Good morning all, Can an IF statement be used with a COUNTIFS? I currently have the following formula that works a treat =COUNTIFS(Range,">="&Start Date, Range, "<="&End Date) This counts everything which falls in a certain date range, but what I need is to incorporate an IF statement which...
18. ### If Error statement

Is it possible to combine an iferror statement and a countifs statement into one? Here is my current countifs: =COUNTIFS(AACH!C:C,">8/31/2019",AACH!C:C,"<10/1/2019",AACH!F:F,"Y")/COUNTIFS(AACH!C:C,">8/31/2019",AACH!C:C,"<10/1/2019") My current statement is returning #DIV/0! which is correct...
19. ### INDIRECT in COUNTIFS and SUMPRODUCT returing error, despite working when they are the only arguments

I have the name of the start of a range in Q1, name of the end of the range in Q2 (for example: Q1 = "Data!\$Y\$2", Q2 = "Data!\$Y\$2000"). I have a criteria in F2 (for example: "Canada"). I have some existing COUNTIFS and SUMPRODUCTS set up like below (using named ranges/cells)...
20. ### Countifs into SUMPRODUCT

=CountIFS('[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!\$A:\$A;">="&'[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!\$A\$1684;'[Direktgeschäft VU - 2019 NeU.xlsx]Daten '!\$J:\$J;B19) Could anyone please help me translate this into a SUMPRODUCT ?

### This Week's Hot Topics

• Problem with Radio Button's format control
I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
• Last Display on userform to a Listbox
[CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
• Rename and move files to a new location
Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
• Help with True/False Formula
Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
• Clear extra characters from a provided range of cells
Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
• Help with Current and highest streaks
Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...