Excel COUNTIFS and COUNTIF with multiple AND / OR criteria

Comments page 26. Total comments: 2056

Aufa Fadhli says:
2019-01-10 at 2:21 am

Hi,

Kindly help me, I have some criteria to be defined from below table:
Master Try 1 Try 2
G G R
G G R
G G G
R R R
R G G

From above you see G (good) and R (reject). What I what to count is correctly decision and falsely decision referring to master. The table will like below:
CORRECTLY FALSELY
G R G R

Please advise for the formula,

Thanks in advance
Aufa
robin says:
2019-01-06 at 3:18 pm

I am trying to create a formula where I am counting only answers that are NA or Pass. I am able to successfully create the formula if i am just counting Pass, but not both. I am not using a range. The formula that works for just pass is: =SUM((COUNTIF(INDIRECT({"B10","B13","B17","B18"}),"Pass")*D10),"0")

How do I also make it add NA?
Gary says:
2018-12-31 at 6:04 pm

looking for some advice, I'm trying to count different values in excel or sheets.

example: counting sick days for employees.

Full sick day being 1
half sick day being 0.5

SICK = 1
SICK/ = 0.5 (this means the first half of the day was sick)
/SICK = 0.5 (this means the second half of the day was sick)

How can I build something that recognizes SICK SICK/ and /SICK and also adds up the different values of each. Thanks in advance.
maqbul says:
2018-12-17 at 4:24 pm

good evening
kindly help me out

i have a excel table included with departments and dates

i have 4 types of departments and dates from jan to dec
i need to combine the BELOW GIVEN BOTH forumla IN ONE like example given below

=COUNTIFS(A2:A7,"Logistics Department")
other for formula is

=COUNTIFS(B2:B7, "="01/01/2018)

MY MAIL ID maqbul2005@gmail.com
1. Mary Trifuntova (Ablebits Team) says:
  2018-12-18 at 9:47 am
  
  Hello Maqbul,
  Please try to apply the formula below:
  
  =COUNTIFS(A2:A7, "Logistics Department", B2:B7, "="&DATE(2018, 1, 1))
Tghanem says:
2018-12-04 at 10:02 am

The project's owner wants to know the percentage of the student pass the exam(per class & subject) in a table where the row is the class and the column is subject. The issue here as the student had repeated the exam different times in both different classes and subjects. The owner is accepting to have one exam result within a three month period if the student repeated the same exam in the same class and for same subject.
I used countifs but its really exhausting exercise for huge data. Is there any other solution ?!!
Tghanem says:
2018-12-04 at 10:01 am

I have huge excel where the data duplicated. The frame of the table has:
1- Student name "Duplicated"
2- Class "Duplicated"
3- Subject "Duplicated"
4- Exam as Pass,Fail.Pending. "Duplicated"
5- Date "Duplicated"
6- Record of data entry is IDENTICAL.
The project's owner wants to know the percentage of the student pass the exam(per class & subject) in a table where the row is the class and the column is subject. The issue here as the student had repeated the exam different times in both different classes and subjects. The owner is accepting to have one exam result within a three month period if the student repeated the same exam in the same class and for same subject.
I used countifs but its really exhausting exercise for huge data. Is there any other solution ?!!
Jaisankar says:
2018-11-20 at 2:05 pm

Dear friends, help to simply my work in excel. I have daily stocks data in A1:A100 for day 1 and B1:B100 for day 2. Now i want to compare B column with A and result to display in C as how many times increased or decreased. i.e 2X times increased, 5X times decreased like.

Is there anyway to setup formula for my requirements...
John Nelson says:
2018-11-19 at 7:21 pm

In each row (twenty schools) I have two columns (B, C) of attendees (student, non-nonstudent). How would I count whether a school is represented by either a student or a non-student (i.e., count if b=>0 or if c=>0)?

I tried =countifs (B3:B22,">0", C3:C22,">0") and tried =countifs (B3:B22,">0") + countifs (C3:C22,">0"). Neither of these worked.
1. Mohammed Ali says:
  2018-11-20 at 11:30 am
  
  Could you please send me the table, so that I can do it.
  1. good, nearer same problem i am facing says:
    2019-12-01 at 2:55 am
    
    good, nearer same problem i am facing
Mohammed Ali says:
2018-11-18 at 11:18 am

Dear,

Please help me with the table. I would like to change the data when I change the Month. It should change only the data from the table. Month is in dropdown list.

October ACC AOP ASC
DS 98.91% 93.33% 93.18%
KD 100.00% 75.50% 88%
HP 86.91% 89.13% 88%

Regards,
MZA
Mandy says:
2018-11-07 at 1:45 pm

I am trying to figure out a formula that if P2 is =1000 add 120 days to the date in M2. I cannot seem to get it to return a correct answer. Any help is greatly appreciated.
1. Mandy says:
  2018-11-07 at 2:44 pm
  
  I should clarify that cell M2 is formatted as a date.
  
  =Countifs(M2,P2,">=1000",M2,120,P2,"<999",M2,90)
  I'm sure my formula is no where close to where it needs to be.
Doug says:
2018-11-06 at 12:32 pm

Partha:
You can count multiple criteria by using the COUNTIFS function.
In your case where the first column is in A67:A81 and the attendance is in B67:B81 put "Mad" in C69, "Dad" in C70 and "Chad" in C71. Then in D69 enter =COUNTIFS(A67:A81,C69,B67:B81,"P")and in D70 enter =COUNTIFS(A67:A81,C70,B67:B81,"P")and in D71 enter =COUNTIFS(A67:A81,C71,B67:B81,"P").
You'll have the attendance for the Nov range next to each name.
PARTHA SARATHI BISWAS says:
2018-11-06 at 8:33 am

how i will calculate the total number of attendance,like Absent and Present in a month? my excel work sheet is given below:-

(A=Absent,P=Present)

01-Nov
SE Name Remarks
Mad A
Dad P
Chan A

02-Nov
SE Name Remarks
Mad P
Dad A
Chan P

03-Nov
SE Name Remarks
Mad P
Dad P
Chan P

Pls.help me...

Thanks.
P.Biswas.
Roberto Avila says:
2018-11-02 at 12:40 am

I am searching on how to count cells based on specific text within a date range.

E.G. I have a column with codes: UA, RO, etc. I have another column with the date the the code was created.

I need to count the number of cells with UA from 09-01-2018 and 09-30-2018.

I find the syntax for counting a cell with the text "code" and how to "sum" numeric values in a date range. I just don't seem to be able to find the syntax that combines both counting cells for specific text within a date range.

Anybody know how?
1. Davin Chhun says:
  2019-08-08 at 6:02 am
  
  Yes I can help u please send that issue to me in E-mail.
  Thanks,
2. Drusey says:
  2019-07-18 at 3:26 pm
  
  I have a very similar issue. did anyone ever respond?
  1. Chanuk says:
    2019-12-08 at 6:50 am
    
    Did u sort it out your query?
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

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

Comments page 26. Total comments: 2056