Question about Microsoft Excel for PC

3 Answers

Excel Formula to count how many of an item that meets 2 other criteria

Posted by on

  • 1 more comment 
  • JAYC3PO Apr 28, 2008

    Just wanna say Cheers to mcc99 & CNewton2 for posting a solution to my problem with an Excel formula. Being a first timer to this i didnt give enough detail to my problem so im going to try again, hope i get the same response.

    laters Jayc3p0

  • motilal May 06, 2008

    countif(A1:A5,"apple",2)

  • Anonymous May 06, 2008

    if(and(m1>=35,m2>=35,m3>=35,"pass","fail"))

×

3 Answers

Lets say your data (fruits) is in range "A1:A8" and you need to know that how many total numbers are mangoes and Pinapples in them.
In Such case we can use the countif( ) function of Excel as following:-
=countif(A1:A8,"Mangoes")+countif(A1:A8,"Pinapples") <>
Hope this help.

Posted on May 03, 2008

  • Level 1:

    An expert who has achieved level 1.

    All-Star:

    An expert that got 10 achievements.

    MVP:

    An expert that got 5 achievements.

    Mentor:

    An expert who has written 3 tips or uploaded 2 video tips.

  • Contributor
  • 51 Answers

Just to point out, if you are trying to get a count of criteria that is based on 2 or more conditions being met, then things get a bit more complicated. COUNTIF doesn't support boolean concatenations of conditions, ie:

=COUNTIF(A1:A8,">2 and <8")

where A1:A8 holds a range of numbers 1 through 8.

This won't work.

So creativity will be called for if that is the case with you. Your approach will be dictated by the exact nature of the criteria and data.

Posted on Apr 24, 2008

  • mcc99
    mcc99 Apr 24, 2008

    P.S. On-line COUNTIF doc is here.

×

  • Level 1:

    An expert who has achieved level 1.

    Hot-Shot:

    An expert who has answered 20 questions.

    Corporal:

    An expert that has over 10 points.

    Mayor:

    An expert whose answer got voted for 2 times.

  • Contributor
  • 26 Answers

Lets say your data (cars) is in range "D1:D21".

You need to know how many are Dodge and Ford in cell E1.

=COUNTIF(D1:D21,"Dodge") + COUNTIF(D1:D21,"Ford)

Hope this helps.

Posted on Apr 23, 2008

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

1 Answer

Countifs formula


COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition.
This COUNTIF formula appears to count only the Employees that have the value "F" in the designated container. ie. Female.

Nov 20, 2013 | Microsoft Excel 2010

2 Answers

Hi, how do I find the 1st 2 letters in a cell and reference it to another. ie A1: 0209JJP001. I thus want to find "02" which is Feb (this would be a vlookup I assume). Therefore the 1st 2 letters...


To reference the first letters in a cell, use the left function. The syntax is =left(cell,#). So, to return the left two letters from cell A1, you use =left(a1,2). You can put that in a cell or incorporate it into some functions.

Jun 12, 2009 | Microsoft Excel for PC

1 Answer

Excel Formula


With data in A1 to C1: A2 =IF(A1>0,1,0) B2 =IF(B1>0,1,0) C2 =IF(C1>0,1,0) A3 =SUM(A2:C2)

Jun 04, 2009 | Microsoft Office Excel 2007

1 Answer

Excel formula


Use the COUNTIF command. The COUNTIF command can count the criteria for a range of cells. Since you can only use it for one range of cells or criteria, you simply add another criteria to the formula as follows: =COUNTIF(AG1:AG5,"X")+COUNTIF(Sheet2!L1:L6,"X")

Apr 10, 2009 | Microsoft Excel for PC

1 Answer

Countifs


The formula in both versions is the same:

=countif(range,value)

i.e. The below formula will increase its count by one if any of the 18 cells it's checking are equal to 1.
=countif(A1:B9,1)

Jan 21, 2009 | Microsoft Office Excel 2003 for PC

1 Answer

Count with 2 or more criteria


Can you do this using a pivot table where columns B & C are Row Fields and Count of B&C is data fields.

Jan 17, 2009 | Microsoft Excel for PC

1 Answer

Count how many times a value appears in a column, based on anothe


Go to the cell you want this total in.
Type this formula:
=SUM(IF(Sheet2!C1:C10="EME",IF(Sheet2!N1:N10=1,1,0)))
make sure you end the formula with CTRL - SHIFT - ENTER which makes it an array formula. If you forget, go back to the cell with this formula and press F2 (to edit the cell) and press CTRL - SHIFT - ENTER to convert it to an array formula (Excel will show a little {...} around the formula).

Dec 21, 2008 | Microsoft Excel for PC

3 Answers

EXCEL FORMULA PC


The solution I've used in similar situations is to create a 3rd column C with the items in column A and column B concatenated.

C2 = A2 & B2
C3 = A3 & B3
C4 = A4 & B4
etc.

Then use COUNTIF function: =COUNTIF(C:C,"FredRed Ball")

Hope this helps.

May 27, 2008 | Microsoft Excel for PC

2 Answers

Excel Formula Help


Name the list as a range. Copy the list to a different location, eliminate duplicate values, then write the formula next to the newly listed items to count the number of occurrences in the original list range. Thanks for rating FixYa!

Apr 22, 2008 | Microsoft Office Professional 2007 Full...

1 Answer

Calculated field in Excel pivot


Count will only work with numbers. So select another box where you want your number and use this formula: =count(x:x) This should give the count number then you will need to find the difference. In another box, use =sum(x-x). This should give you your difference.


If this helps you, please rate me. Thanks, Patty

Dec 17, 2007 | Business & Productivity Software

Not finding what you are looking for?
Microsoft Excel for PC Logo

175 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Business & Productivity Software Experts

Brian Sullivan
Brian Sullivan

Level 3 Expert

27725 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18298 Answers

Sudeep Chatterjee
Sudeep Chatterjee

Level 3 Expert

3267 Answers

Are you a Microsoft Business and Productivity Software Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...