Excel COUNTIFS and COUNTIF with multiple AND / OR criteria

Comments page 27. Total comments: 2074

JVal says:
2018-10-22 at 9:13 pm

Good Afternoon,
I have created a checklist in Excel with checkboxes under each heading (3 headings) with the option of TRUE,FALSE or #N/A (as some of the items may not be applicable). I have assigned a cell to each check box. I am trying to capture the completion rate of the items required in my checklist (both when required and/or applicable information has been received). I have used COUNTIF to capture the TRUE data in each subheading, but I cant seem to figure out a formula to capture the required data and additional data when applicable to score as available points and actual points. Any ideas?
Albert says:
2018-10-22 at 7:35 am

how can i found the dates which is marked as leave in one month attendace.

like we have mentioned all dates from 1 to 30 Aug'18 in one row continuesly
under that one employee attendance was mentioned like present and leave

only two days 17 Aug 18 and 27 Aug 18 was on leave.

how can i found the leave dates using formula in excel
atif ali afandi says:
2018-10-14 at 5:26 pm

i have to prepare 24 entries (hourly basis) daily sheet and at the end of month i have to add up 00:00 hrs of every day reading in next sheet. is there any formula which i give it 1 ference cell and it can count and add every 24 cell.
atif ali afandi says:
2018-10-14 at 5:23 pm

i have to prepare 24 entries (hourly basis) daily sheet and at the end of month i have to add up 00:00 hrs of every day reading in next sheet. is there any formula which i give it 1 ference cell and it can count and add every 24 cell.
khadharshah says:
2018-10-06 at 10:30 am

Dear all,

I have faced the problems with xl operations
for example i have 2 sheets one is abstract and one more is detail sheet
i want to bring counts from details sheet to abstract sheet how it is possible
Details sheet columns we have like module column(A,B,C,D), Types(CR,PR,QR), priority(high,low,medium) how to shown counts in abstract sheet ->(module Wise, no. of CR, no of PR, no QR)counts
Please explain the formula
Rizwan says:
2018-10-04 at 6:24 am

Hi,
I need a formula.
4 columns (Name,Purchase,Sale,City)
I want if purchase cell contain specific word like Nil other than number then count nil with concatenation of city like
| date 04-10-2018 | |Date 05-10-2018 |
Name Purchase Sale City | Name Purchase Sale City
Peter 10 5 Manchester| Peter Nil Denmark
Rock Nil Denmark | Rock Nil Manchester
Sam Nil NewYork | Sam Nil Denmark

| date 06-10-2018 | |Date 07-10-2018 |
Name Purchase Sale City | Name Purchase Sale City
Peter 10 5 Manchester| Peter Nil NewYork
Rock Nil Denmark | Rock 10 5 Manchester
Sam Nil NewYork | Sam Nil Denmark

result should be show

column name
count of Nil NewYork | count of Nil Denmark | count of Nil Manchester
Peter 1NewYork 1Denmark
Rock 2Denmark 1Manchester
Sam 2NewYork 2Denmark

Please give me formula of this statement.
Mac Sageer says:
2018-09-26 at 6:55 pm

Hi all... I'm looking to countif unique values that occur on a certain date with one type of criteria also occur across a separate date range with different criteria at least once, so if my data set (for columns A, B, C) looks like this

Date ID Activity
9/1/18 266 Registered
9/2/18 233 Registered
9/3/18 266 creative
9/4/18 235 budget
9/5/18 267 creative
9/6/18 266 creative
9/7/18 233 creative
9/8/18 287 creative
9/9/18 298 budget
9/10/18 288 budget

I want to find unique instances of the IDs that registered on 9/1 but then show how many of the unique IDs that registered on 9/1 actually did at least one activity (doesn't matter which) in the first, second, third week following 9/1).

This (example of week one) isn't working for me:
=COUNTIFS($A:$A,"=9/1/18,$C:$C,"registration",$A:$A,">=9/1/2018",$A:$A,"<=9/08/2018",$C:$C,"registration")

Can anyone help?
1. Joseph Lewis says:
  2018-10-09 at 4:24 pm
  
  Please remove my thread. I seem to be making progress combining matrix arrays back into columns. That seems to be the basis for solving this equation. I found a good example here:
  
  https://exceljet.net/formula/count-rows-that-contain-specific-values
Joseph Lewis says:
2018-09-25 at 4:52 pm

Seeking a bit more advanced application of this logic. There are many approaches to summarizing data; however, COUNTIFS seems to offer a unique function when comparing data against itself. For instance assume the following data set:

Material Order Type
a YO03
b YO01
f YO03
d YO03
a YO01
f YO03
d YO01
f YO03
a YO01
a YO03

If I enter a function as follows:

=LARGE(COUNTIFS([Material],[Material],[Order Type],"YO03"),1)

The result is 3, the count of the most frequent material with Order Type YO03. Combining this with INDEX and MATCH it can return the value associated with 3 as follows:

=INDEX([Material],MATCH(LARGE(COUNTIFS(([Material],[Material],[Order Type],"YO03"),1),COUNTIFS(([Material],[Material],[Order Type],"YO03"),0))

This formula returns material f, the material with the most orders of type "YO03". My trouble is I want to evolve this formula to rank based on 5 criteria. I would like a formula that calculates a RANK on five Criteria, by doing a COUNTIF on the same above, then adding a similar rank for order quantity, another order type, and respective order quantity, followed by the row as a tie breaker. I've managed to do this with a helper sheet but I hope to do this in one step. My helper formula looks like this:

=IF([@Material]="","",1
+COUNTIFS([Emergency Orders],">"&[@[Emergency Orders]])
+COUNTIFS([Emergency Orders],[@[Emergency Orders]],[Emergency Quantity],">"&[@[Emergency Quantity]])
+COUNTIFS([Emergency Orders],[@[Emergency Orders]],[Emergency Quantity],[@[Emergency Quantity]],[Stock Orders],">"&[@[Stock Orders]])
+COUNTIFS([Emergency Orders],[@[Emergency Orders]],[Emergency Quantity],[@[Emergency Quantity]],[Stock Orders],[@[Stock Orders]],[Stock Quantity],">"&[@[Stock Quantity]])
+COUNTIFS([Emergency Orders],[@[Emergency Orders]],[Emergency Quantity],[@[Emergency Quantity]],[Stock Orders],[@[Stock Orders]],[Stock Quantity],[@[Stock Quantity]],[Row],"<"&[@Row]))

I'm struggling to introduce SUM(IF(s)) into this statement and maintain the analysis by material. And perhaps this is just too darn complex to convey in writing?
1. Joseph Lewis says:
  2018-10-08 at 1:58 pm
  
  Here is my data:
  
  ROW Material Order Type Qty
  1 a Emergency 1
  2 b Stock 2
  3 f Emergency 3
  4 d Emergency 2
  5 a Stock 6
  6 f Emergency 3
  7 d Stock 2
  8 f Emergency 6
  9 a Stock 4
  10 a Emergency 3
  
  I'm working to assign a multilevel rank in one cell to return the material that has the greatest number of emergency orders, than Emergency Qty, Stock Orders, and Stock Qty with ROW as a tie breaker. I've managed to do this with a help column but the analysis takes to long when working through 500k lines. So I'm trying to do this to identify the top 25-50 materials in one step. So far I've assembled the following code but I cannot seem to SUM by ROW the matrix data to return the ranked match. Here is my code so far:
  
  {=INDEX(Orders[Material],MATCH(0,
  (((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))<(TRANSPOSE(COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))))*1+
  
  IF(((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))=(TRANSPOSE(COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency")))),((SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))<(TRANSPOSE(SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))))*1)+
  
  IF(((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))=(TRANSPOSE(COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency")))),IF(((SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))=(TRANSPOSE(SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency")))),((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Stock"))<(TRANSPOSE(COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Stock"))))*1))+
  
  IF(((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))=(TRANSPOSE(COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency")))),IF(((SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))=(TRANSPOSE(SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency")))),IF(((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Stock"))=(TRANSPOSE(COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Stock")))),((SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Stock"))<(TRANSPOSE(SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Stock"))))*1)))),0))}
  1. Joseph Lewis says:
    2018-10-08 at 2:17 pm
    
    Correction to the equation (I don't see where I can upload the spreadsheet in the comment section):
    
    {=INDEX(Orders[Material],MATCH(ROW()-2,SMALL(SUM(
    (((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))<(TRANSPOSE(COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))))*1+
    
    IF(((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))=(TRANSPOSE(COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency")))),((SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))<(TRANSPOSE(SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))))*1)+
    
    IF(((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))=(TRANSPOSE(COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency")))),IF(((SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))=(TRANSPOSE(SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency")))),((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Stock"))<(TRANSPOSE(COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Stock"))))*1))+
    
    IF(((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))=(TRANSPOSE(COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency")))),IF(((SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))=(TRANSPOSE(SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency")))),IF(((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Stock"))=(TRANSPOSE(COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Stock")))),((SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Stock"))<(TRANSPOSE(SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Stock"))))*1))))),ROW()-1),0))}
    1. Joseph Lewis says:
      2018-10-11 at 5:04 pm
      
      I got this ridiculous thing to work...with 4 level ranking. Now to add a fifth level tie breaker for materials that have like order activity. Let me know if you have any suggestions. Changing MATCH(0 to MATCH(ROW() will allow next level matches in the array. Here's the code:
      
      =INDEX(Orders[Material],MATCH(0,
      
      IF((--(MMULT(--((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))0))))>0,1,0)+
      
      IF((--(MMULT(--((SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))0))))>0,(TRANSPOSE(SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Emergency"))),0)),--(ROW(Orders[Material])>0))))>0,1,0)+
      
      IF((--(MMULT(--((COUNTIFS(Orders[Material],Orders[Material],Orders[Order Type], "Stock"))0))))>0,1,0)+
      
      IF((--(MMULT(--((SUMIFS(Orders[Qty],Orders[Material],Orders[Material],Orders[Order Type], "Stock"))0))))>0,1,0),0))
Kelly says:
2018-09-24 at 5:07 pm

I'm having an issue here:

for those names that fall under P:P may not match F4 exactly, because of an initial in the name, how would I write this to count if name is similar when looking up on different tab?

=IFERROR(COUNTIFS('Issues w-out Action Plans'!K:K,"< 30",'Issues w-out Action Plans'!P:P,'MoEVM Summary Level'!F4),"0")
Dawnica Green says:
2018-09-24 at 1:27 pm

Can someone help me make this formula work?
=COUNTIF(B33,E33,H33,K33,N33,Q33,T33,W33,Z33,AC33,AF33,AI33,AL33,AO33,AR33,AU33,AX33,BA33,BD33, ">70%")
I only want these cells condidered in the count if because they contain student data for a paticular test.
1. Doug says:
  2018-09-24 at 2:07 pm
  
  Dawnica:
  The COUNTIF function should be setup like COUNTIF(Range,Criteria). So in your case if the data range is B33:BD33 and the criteria is values greater than 70 the formula looks like this:
  =COUNTIF(B33:BD33,">"&70)
  1. Dawnica Green says:
    2018-09-24 at 3:19 pm
    
    Thanks for the help!
    But what id there are cells in this row that I do not want included in the countif. is it possible to exclude cells?
    
    That is, in my formula attempt I wanted to get the number of students who scored above 70 on test 1
    When I put in the formula you provided it every cell in the row so it is counting scores above 70 for all tests given so far. Is there a way to do this or would I need a different type of formula entirely?
    1. Doug says:
      2018-09-24 at 4:08 pm
      
      Dawnica:
      What are the other criteria for the tests you want to count? Are they by date or a test number or something else?
      Which version of Excel are you using?
woldemariam says:
2018-09-24 at 12:23 pm

Dears,
I have three criteria in column "C", "D" and "E"to consider for customers. i want to have another column "F" wherein i would like to get one value after which i shall use the command. But how can i write an if statement which is basically"
If the value in column "C" is zero, consider the value in Column "D" and if column "D" is zero, then consider the value in column "E".
Thank you
Aaron S. says:
2018-09-23 at 11:04 pm

Hi. I'm looking for help writing a coutif formula. I have a column with a list of dates. I'm looking to count all dates that are greater than TODAY(). I don't know how to write the condition to check each cell in the range against the TODAY() function. Please assist. Thank you!
1. Doug says:
  2018-09-24 at 11:48 am
  
  Aaron:
  Here's the formula to accomplish what you want.
  =COUNTIF(J3:J11,">"&TODAY())
  1. Doug says:
    2018-09-24 at 11:56 am
    
    Aaron:
    You could get a little fancier with:
    ="Dates Greater Than Today"&" "&"="&" "&COUNTIF(J3:J11,">"&TODAY())
    1. Doug says:
      2018-09-24 at 12:01 pm
      
      Aaron:
      Or finally you could put the TODAY() function in another cell and reference that cell in the formula like this:
      ="Dates Greater Than Today"&" "&"="&" "&COUNTIF(J3:J11,">"&K3)
rabin karki says:
2018-09-20 at 9:40 am

I have a problem...
1,2,2,2,3,3,6,6,7,8,9= 7 .how to count without repeating the numbers. I mean I must count repeated value only once . I have to get result as 7 , how??
sanan says:
2018-09-12 at 11:26 am

i want to get formula which count same text in a list and decleared it as "good",or "bad"
Griselda Piedra says:
2018-09-11 at 8:46 pm

Hi,

I'm currently working on trying to find out how many of my students enrolled in week 1 of my course completed and passed my class.

I am using =countifs(class start date range, ">="&A1, class start date range, "<+"&B2, Student Status range, "completed class and should advance") I get 0 when I have 2 in my sample data that started the course within the date range and advanced.

What am I doing wrong?

Thanks in advance for your help!
Divakara P Jain says:
2018-09-06 at 7:03 pm

Hidears
I have put numbers in A1 and A2 cell and put space in A3 cell and in A4 cell put, =countblank(A1:A3), it returned the value zero and later in A5 cell used =clean(A3) and why not the value in A4 cell showing one!? please help, just started learning excel..
Kerry Watts says:
2018-08-30 at 12:54 am

I have a large spreadsheet which tracks my daily food and fluid intake.

I have a two columns that contain the beverage and the amount consumed (see attached)

0.4 Beverage - Coffee - Flat White with Skim Milk - Large - Coffee Shop
0.3 Beverage - Coffee - Flat White with Skim Milk - Medium - Coffee Shop
0.2 Beverage - Coffee - Flat White with Skim Milk - Small - Coffee Shop
0.5 Beverage - Soft Drink - Coke - No Sugar
0.5 Beverage - Soft Drink - Pepsi Max - No Sugar
0.3 Beverage - Vegetable Juice - Low Sodium - V8
0.3 Beverage - Coffee - Flat White with Skim Milk - Medium - Coffee Shop
0.4 Beverage - Soft Drink - Pepsi Max - No Sugar - Vanilla
0.3 Beverage - Coffee - Flat White with Skim Milk - Medium - Coffee Shop

I want to count the number of times each similar beverage occurs each day, multiply it by the amount consumed to obtain a total for each beverage each day.
I have tried countifs, countif, vlookup, match and index and array functions but I cannot seem to get the logic right.

Help me Obi Wan you're my last hope!! :-)
CPH says:
2018-08-29 at 10:09 am

I was trying for an option to count sequence, including duplicates in date from current to earliest.
Where seq on C when contains 2 or above , the date sequence to be found.

PRIMARY DATA UNIQUE_PRO_PUR SEQ ON C SEQUENCE ON B
APPLE 1_1 04/11/2017 0 1
ORANGE 1_55 04/02/2017 2 2
GRAPES 1_57 04/11/2017 0 1
ORANGE 1_55 04/03/2018 1 2
K-chan says:
2018-08-13 at 7:08 am

Hello! I'm trying to get my spreadsheet to add the following up:

August 2018 Census Regular Agencies/ Third Party TOTAL
Work Related Incident / Accidents

Lacerated Wounds 2 0 2
Incision Wound 0 0 0
1. K-chan says:
  2018-08-13 at 7:14 am
  
  Had to resend bec comp is not cooperating.
  
  Hello! I'm trying to get my spreadsheet to add the following up:
  
  August 2018 Census Regular Agencies/ Third Party TOTAL
  Work Related...
  
  Lacerated Wounds 2 0 2
  Incision Wound 0 0 0
  
  Regular should read '1' instead of '2'; I wanted it to count an entry for the month of August if the employee is "Regular" or "Probationary", and if it was the same category as on the leftmost column.
  
  My formula is: =SUM(COUNTIF('WORK ACCIDENT LOG'!A:A,(MONTH(8))),(COUNTIF('WORK ACCIDENT LOG'!G:G,{"Regular","Probationary"})),(COUNTIF('WORK ACCIDENT LOG'!I:I,AUGUST!W51)))
  
  I've been trying Sumproduct, max(sum. Doesn't really help. Would appreciate any advice. Thank you!
Manoj says:
2018-08-05 at 3:07 am

I want add "OR" condition between 2 countifs

countifs($A:$A,">1",$A:$A,"80",$B:$B,"<100")

How can i acheive this. Please help.
RocHowl says:
2018-08-02 at 2:34 pm

Hi,

I am currently using a timesheet to see how much of my day is spent doing certain tasks. It looks something like this:

14:43(start) to 15:24 (finish) ADMIN
15:24 to 16:59 Payment Recons

I now need to use countifs/sumifs to see how much time I spent doing admin. I'm sure I can use the same formula to see time spent doing admin in a day or in a month, etc.

Thanks so much!
Miguel says:
2018-08-01 at 7:17 pm

I need a formula to count and generate a new column with the data.
If I have a column with repeat numbers:
123
123
456
789
789
New column is:
1 of 2
2 of 2
or
1-2
2-2
Thank you in advance,
Miguel
Stu says:
2018-07-30 at 4:13 pm

My issue is that I have 2 columns with date ranges (a start date and an end date.)
These can span multi-month and in a few instances multi-year.
I'm trying to create a formula that would determine how many instances of each of these columns land in January of a specific year (or February,etc. Trying to track how many of these fall into a month.)

Thanks for your time!
1. Doug says:
  2018-07-31 at 12:17 pm
  
  Stu:
  The above article explains this and more. I recommend you read it if you want to understand more about Excel. However, here's a synopsis.
  Where the dates are in the range F64:F76 and you want to count all the August 5, 2018 dates use this formula:
  =COUNTIF(F64:F76,"8/5/18")
  Same range and you want all the dates from August
  =SUMPRODUCT(1*(MONTH(F64:F76)=8))
  Same range and you want all the dates from August 2018
  and your Excel version supports COUNTIFS:
  =COUNTIFS(F64:F76,">8/1/18",F64:F76,"<8/31/18")
Andrew Dean says:
2018-07-26 at 4:05 pm

Hi

I am trying to do the following.
Example:
Row 4 - 80, 79, 56, BLANK
Row 5 - BlANK, 34, 56, 88

I want to be able to count the range in row 4, however, is a cell is blank then use the corresponding cell on row 5.

The answer would be = 303 for the above.

HELP PLEASE

Andy
Erik says:
2018-07-18 at 1:37 pm

I need help. I am trying to take column B with 9 different arrays such as Air Refueling WG, Mission Support Group, Maintenance Group, etc... Then in Column G I have three arrays.. Current, Overdue and NPNP.. what I am trying to do is break down the 900 cells to show me who is current and overdue per each separate array in Column B.

For Instance: Column b has 9 Air Refueling WG cells and 7 are current and 2 are overdue. So I need a formula that will pick out the Air Refueling WG people and show me how many are current and how many are overdue. and then do that for each separate group in column b.

Can this be done??
Mirza Farooq says:
2018-07-11 at 12:48 pm

Hello
I am working on sheet to get customer attendance that how many days customer come into company sales register. specimen data is as follows;

Cloumns A B C
Customer Date Attendance(Result Col)
Customer A 6/1/18 Need Count Result like 1
Customer A 6/1/18 Need Count Result like 1
Customer B 6/1/18 Need Count Result like 1
Customer A 6/2/18 Need Count Result like 2
Customer A 6/2/18 Need Count Result like 2
Customer C 6/2/18 Need Count Result like 1
Customer B 6/3/18 Need Count Result like 2
Customer A 6/3/18 Need Count Result like 3
Customer A 6/3/18 Need Count Result like 3
Thanks & Regards
Mirza Farooq
1. Mirza Farooq says:
  2018-07-12 at 6:22 am
  
  how to upload excel file?
glynis wray says:
2018-07-10 at 4:17 pm

i would like to write a formula in excel where eg. each time a person clocks in after 8:30 am then to time is accumulated for the month. eg. say the clocks in 8:45am then is shows time loss as 45 minutes
Vijayakumar says:
2018-07-10 at 7:23 am

Hello,

I nedd one help to you.

can you please provide me advice.

I have vendor & payment tracking excel sheet.

I entered the sales details in vendor sheet.

we are selling the materials to lot of vendors.

coming to the point.

Vendor sheet i maintaing Vendor name, Invoice date, actual value, GST and net value.

Payment sheet i entered only Vendor name and Invoice date only.

Both sheet compare and coming to the total value.

is it possible to excel.

Please give me the formula.

Regards,
Vijay
Geoffrey says:
2018-07-09 at 2:26 pm

I need a program that can generate for me a criteria based on three issues in rows a,b and c for 20 categories as columns such that cell will have 3 scenarios in each column and row produced to the 20 columns.how many cells contents will i have in total 20x3x3? or 20x3x3x3? which branch of maths is this? can it be done in excel? if not which program?
kind regards
Geoffrey
Geoffrey says:
2018-07-08 at 12:58 pm

I need a program that can generate for me a criteria based on three issues in rows a,b and c for 20 categories as columns such that cell a will have 3 scenarios in each column and row produced to the 20 columns.how many cells contents will i have in total 20x3x3? or 20x3x3x3?
which branch of maths is this?
Lynn says:
2018-05-28 at 3:44 pm

Hi,

I have a survey with the following columns: User, Role, Q1 (Like, Dislike, Neutral), Q2 (Like, Disklike Neutral).

I have my 'countifs' working with named ranges (Role, Q1, Q2), such that I have

=COUNTIFS(Role, "Accounting", Q1, "Like")
=COUNTIFS(Role, "Accounting", Q1, "Neutral")
...
...
=COUNTIFS(Role, "Payroll", Q1, "Like")
=COUNTIFS(Role, "Payroll", Q1, "Neutral")
...
...
=COUNTIFS (Role, "Accounting", Q2, "Like")
etc etc
etc etc

This works, but it is time consuming to manually input the roles and response as the title for each line, and then update the formula to have the role/response each time.

I'm wondering if there is a way to kind of combine the 'subtotal', or somehow 'group' this information, or use this formula differently so that I can more easily get the counts without having to define the role manually each time ex. like it would just see 'at each change in Role, use the countif"

So the output would be:
Accounting Like - 10
Accounting Dislike - 20
Payroll Like - 15
Payroll Dislike - 5

Hope my questions is clear enough!
jatin arora says:
2018-05-25 at 6:41 am

I am trying to plot a formula to count month names (i.e. Jan, Feb, Mar, Apr)and have prepared a drop down list on cell Q16 with month names. I want that if I select a month it gives me the count of that month only, other wise if I select All it would give me the count of all months. Please help

=COUNTIFS(Sheet1!R:R,Q16,Sheet1!R:R,"Q16")
Heather says:
2018-05-24 at 1:02 pm

I'm close to understanding by reading all of the above, but I have a question. I need to compare two columns, counting the number of times a name in column A matches a name in column B. Column A being the constant unchanging column. There are MANY names so spelling them all out in "" is not efficient, I would love to cell reference them if possible. Thank you!
1. Heather says:
  2018-05-24 at 1:11 pm
  
  for example...count how many times the names from B match A:
  A B
  John Smith James Doe
  Jane Smith Eric Li
  James Doe George James
  Jana Doe Mary Patt
  Eric Li Tom Wright
  Hilda Smyth Mike Smith
  George James Ryan Garrett
  Ryan Garrett Greg Joseph
  1. Doug says:
    2018-05-24 at 8:23 pm
    
    Heather:
    If you want the number of times the names are duplicated I think this will work:
    =SUMPRODUCT(COUNTIF($A$2:$A$10,$B2:$B10))
    Where the data is in columns A and B cells 2 to 10.
Sunder says:
2018-05-22 at 6:41 pm

What is the formula to add alternate cells if we want to add cells like a1, c1, e1,.....more than 50cells
1. Doug says:
  2018-05-22 at 8:18 pm
  
  Sunder:
  The formula to add cells in every other row is an array formula so after you enter it into the cell where you want the result to show be sure to select the formula in the formula bar and press CTRL+SHIFT+ENTER. When you do you'll see the curly brackets around the formula indicating Excel recognizes it as an array.
  You can change the $A$1:$A$100 or $A$1:$B$100 to the range you need.
  Formula to add every other row in column A is:
  =SUMPRODUCT((MOD(ROW($A$1:$A$100),2)=MOD(ROW($A$100)-1,2))*($A$1:$A$100))
  Formula to add every other row in column A and B is:
  =SUMPRODUCT((MOD(ROW($A$1:$B$100),2)=MOD(ROW($A$100)-1,2))*($A$1:$B$100))
Ramakrishnan says:
2018-05-21 at 2:27 am

i have to extract multiple records from the large range of data with one criteria, i have tried with various formulas, could you please support us

Make Customer Name Status Quote No/PO No Date Part No
TVT L & T Kobelco Open 36 14-05-2018 BSF2250-55
TVT L & T Kobelco Open 36 14-05-2018 BSF2650-70
TVT L & T Kobelco Open 36 14-05-2018 BSF3666-70
TVT L & T Kobelco Open 36 14-05-2018 BSF4276-55
TVT L & T Kobelco Open 36 14-05-2018 BSF56100-80
BPK Flowserve MM Closed 423974 11-05-2018 B7M-180240439
BPK Flowserve MM Open 423974 12-05-2018 P21i-190M
BPK L & T Kobelco Closed 3100001603 12-05-2018 484C-520
BPK L & T Kobelco Closed 3100001603 08-05-2018 483C-450
BPK L & T Kobelco Closed 3100001603 08-05-2018 483C-430
BPK L & T Kobelco Closed 3100001603 08-05-2018 483C-390
TVT Maha Hydraulics Open 33 10-05-2018 JAWS - VICE KURT
TOOLFLO Avalon Tech Open 32 08-05-2018 MTECS 06032C12 32UN AC22R
TOOLFLO Sundaram Clayton Open 31 08-05-2018 MTEC 0606C9 27NPTF AC22R
TOOLFLO Sundaram Clayton Open 31 08-05-2018 MTECZ 08076C10 27NPTF AC22R
KURT Sai Engineering Open 30 07-05-2018 KURT DX6
Haimer Auto Bhan Closed 29 07-05-2018 40.521.16
Haimer Auto Bhan Closed 29 07-05-2018 81.160.06
TOOLFLO Q2Q Solutions Open 28 07-05-2018 MTECZ 1009C28 1.75ISOAC22R
TOOLFLO Q2Q Solutions Open 28 07-05-2018 MTEC 0808C28 1.75ISO AC22R
TOOLFLO Flowserve MM Open 25 03-05-2018 LN25DIR8UN AH725
Haimer Reep Industries Open 23 03-05-2018 F1004NNL0300CAA

i need to extract report based on Make or Customer
1. Doug says:
  2018-05-21 at 11:43 am
  
  Ramakrishnan:
  Does one report need to contain Make or Customer?
  Do you need one report to contain Make and another report to contain Customer?
  Does your Excel version support creating tables?
  What's the number of rows you need to search at one time?
Bill H. says:
2018-05-16 at 3:56 pm

Range of cells Sheet1 G5 to G3208 if the condition "B4" is met in one or more of these cells I need the Sheet1 O5 to O3208 checked for a condition of "reject" on the same ROW of any "B4" conditions and the sum placed at Sheet2 D4 can anyone help me out with this ?
Moses says:
2018-05-16 at 3:40 pm

Good afternoon

Please assist. How do i count number of times data repeated itself in a row and in a column.

Thank you
Mike A. says:
2018-05-14 at 5:35 pm

Hi there,

I'm hoping to get some (hopefully simple) help with COUNTBLANK when adding up blanks within multiple ranges. It seems I have this setup right, but why on earth is it counting 12 instead of 6?

https://content.screencast.com/users/mradamski80/folders/Jing/media/92ec9756-87ac-45e6-8776-c6044ad09d2a/2018-05-14_1222.png

P.S. I cannot share the document legally. Sorry :/
Akhilesh says:
2018-05-03 at 2:11 pm

I have a small query to you but big for me. actually i have a data of whole month. I need the count of failed item which are continues failed more than 3 times. for any example 1may to 3 may Failed then i will get count 1, or if first 2 days failed but on 3rd day got success count will be 0. Like that might be in the mid of month same kind of situation will raise. So it will sum count.

Much appreciate if you can help me.
Praveen Pillai says:
2018-05-03 at 10:50 am

Could I use COUNTIFS to determine how many entries in a multiple-column range match a date in another column?
eg.
A4:B35 against dates in N4:N35

If not what would be the alterntive?

I need to count how many payments were made in April and how many were made in May.
Ashish Rawat says:
2018-05-02 at 7:39 am

please share how to use formula on this condition =COUNTIF(AND
Igor says:
2018-04-20 at 6:25 pm

COUNTIFS does not work as you have presented here.
I have two sets of variables. I want to count (for instance) how many of them have the string "njem." in A:A and the number 1800 in D:D.
The solution suggested here is wrong. It gives me an error message.
COUNTIFS seem to need semicolons separating ranges and criteria, not commas. And even with semicolons it doesn't work. It gives me a "#NAME?" message.
Are we talking about the same programme here?
karen says:
2018-04-17 at 2:59 pm

Hi - I have looked at all the info above but I am still having problems. I need to work out the number of times something occurs

I have one column that has dates that range from 01/08/2017 to 31/07/2020

I have a second column that has numbers 1, 2 or 3

I want to work out how many times the number 3 occurs between the date range 01/08/2018 and 31/07/2019

Can you help ..... thank you
Justjim says:
2018-04-16 at 10:11 am

Hi,

I have an excel sheet with Column A = Due Date and column B = Delivery Date. I want to count how many times the Delivery date is before Due Date. Is this formula correct?

=COUNTIFS(B2:B20,"<="&A2:A20)

Doesn't seem to work.

Thanks.
Girja says:
2018-04-12 at 8:04 am

Hi,

I have excel sheet containing following columns:

1. Bill Date 2. Bill Amount 3. Clients Name 4. Sales Team

Now question is that we want a total bill amount of client name and sales team name wise.
can you help us.
1. Doug says:
  2018-04-12 at 4:09 pm
  
  Using the data, I would first create a table. Then using this table, I would create a pivot table. Using a table as the data source for your pivot table has definite advantages. For example, each time you add data to the table and then refresh the pivot table, the results using the new data is shown in your pivot table. As your data changes you can dynamically view your data. If pivot tables are new to you, search the web for "Use a table for your pivot table".
Stuart says:
2018-04-06 at 8:45 am

I need a formula to add total points score for individual sales people:

sales points
CC 1
AB 3
AB 3
AB 1
AW 1
AW 1
JL 3
AB 1
AB 1
AB 3
AB 1
AB 1
AW 1
AW 1
AW 1
AW 1
AB 1
AW 1
AW 1
AW 1
AW 1
JL 1
JL 3

How can I do this I have tried a countif formula, but doesn't seem to like it.

Thanks
HH van den Berg says:
2018-04-06 at 8:18 am

My Formula2 {=SUM(COUNTIFS(C5:L5;$O$5:$O$8))} worked within minutes. Exactly what I needed. 06 april 2018, Hoogeveen,NL
chen says:
2018-04-03 at 1:58 pm

Hello,

I would like that Excel counts the number of times it finds a combination of several criteria of the same column.

Let me explain:

For example, I want Excel to search in column A to find the words "apple","orange" and "banana" if in column B, the cells for apple, orange and banana contain the word "ripe". So basically, I want to say "Excel please go to column A and look for the word "apple" if the corresponding column B has the word "ripe". Then, go back to Column A and if you also see the words "orange" and " banana" with the word " ripe" in Column B, count that as "1". Therefore, apple = ripe, orange = ripe and banana = ripe would be counted as 1. If it has just orange = ripe and banana = ripe, it would be counted as zero (0).

When I use the function:=SUM(COUNTIFS($C$2:$C$11, {"cancelled", "pending", "in transit"})) as in the example above, it counts each entry ("cancelled", "pending", "in transit") as a single unit instead of as one unit containing the three criteria.

I hope you understand what I am asking.

Thanks in advance.
Naren says:
2018-04-01 at 12:55 pm

Budget vs achievement the incentive will be, if 111% is 7000

please somebody can help me to writ the formula
Aaron says:
2018-03-24 at 11:48 am

Hi,

I am counting from a random list but when the formula reaches a specific amount I want it to stop. Say I have 6 items but I want the cell to only count 3. How to resolve?

=if(COUNTIFS('SF1 Accident Log'!C12:C1000,ʺAvatarEvilʺ,'SF1 Accident Log'!D12:D1000,ʺReprimandʺ) >=4,COUNTIFS('SF1 Accident Log'!C12:C1000,ʺAvatarEvilʺ,'SF1 Accident Log'!D12:D1000,ʺReprimandʺ) -3 , ʺʺ)

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

Comments page 27. Total comments: 2074