rss
Search : in
By : Relevance Date Username
Statut : Not resolved

Excel COUNTIF with 2 conditions

anithNair15, on Wednesday April 9, 2008 12:47:15 PM
Hello,


Does anyone know how to create a total using 2 conditions from COUNTIF statements. I have a worksheet that has 2 seperate countif statements ( 1.Total up the number of times "L" appears in colum J) (2.Total up the number of times "D" appears in column F). However I need a formular that says give me the total number of times where the item+ "L" in column J and "D" in column F.


Thanks for your help!
Configuration: Windows XP
Internet Explorer 6.0
Reply to anithNair15  Report this message to moderators Go to last message

1


  • This message seems useful, vote!
  • Report this message to moderators
Sandira, on Wednesday April 9, 2008 03:14:47 PM
Hello


If it's nothing more than a simple sum you can either sum the cells that have your 2 COUNTIF() functions or use a single formula such as:

=SUM(COUNTIF(F:F,"D"),COUNTIF(J J,"L"))

however, if you need to count the number of times D and L appear in the same row, then you need to use an array formula.

Enter this formula in a cell and hit Ctrl-Shift_Enter to make brackets {} appear around the formula. You can't just type the brackets in , you have to use Ctrl-Shift-Enter to make it an array formula. If you edit the formula, you have touse Ctrl-Shift-Enter again.

=SUM((F1:F100="D")*(J1:J100="L"))
   
Reply to Sandira

2


  • This message seems useful, vote!
  • Report this message to moderators
Ben S, on Thursday July 31, 2008 08:44:00 PM
I saw your posting about the 2 sumif functions in 1 sum function and though you might be able to help.

I have a database of former employees for 4 shifts (A,B,C,D) and I'm trying to track the reason for leaving, and see if I can pit a particular reason for leaving with a particular shift. It's nice to know that for example 70% of my employees are leaving on shift B (which I can already do), but with that I'd like to know the main reason that the employees on shift B are leaving. I've narrowed down the reason for leaving to 8 validated categories with drop down menus. So For example I have 2 columns that look like this.

I've got more data than this (about 96 former employees) but this should give you an idea of what my sheet looks like.

Shift Reason For Leaving

A Inappropriate Conduct
B Inappropriate Conduct
A Inappropriate Conduct
C End of Contract

And I want to for example be able to write a formula that will tell me how many people on shift A left because of innappropriate conduct(2 in this case), or how many on shift C left because of Poor Work output (in this case 0)


Kind of confusing I know, but I'm having trouble finding an answer. Any suggestions? Thanks a ton!
   
Reply to Ben S

3


  • This message seems useful, vote!
  • Report this message to moderators
 Rob, on Thursday August 7, 2008 06:26:04 PM
Not a perfect solution, but will work in your case... such a small dataset...

Order by column "Shift" then by column "Reason for leaving" and simply count
   
Reply to Rob
Ask your question Reply
Software found in the relevant downloads
Download Excel Viewer 2007 Excel Viewer - With Excel Viewer, you can open, view, and print Excel workbooks, even if you don't have Excel installed. You can also copy...Category: Office suite
License: Freeware/gratuit
Download Ad-aware 2008 free 7.1.0.8Ad-aware 2008 free - Ad-Aware 2008 free is an anti-spyware, this utility serves to clean your system of "spyware" and "malware" Ad-Aware 2008...Category: Anti-Spyware
License: Freeware/gratuit
Download PSP Video 9 2.25PSP Video 9 - PSP Video 9 is a free PSP video converter that converts video files, YouTube videos, movies and DVD's so you can play them...Category: Video editing
License: Freeware/gratuit
Download Skype 3.8.0.154Skype - Make free calls from your computer to other people on Skype, cheap to phones and mobiles across the world. The sound quality...Category: Telephony / VoIP
License: Freeware/gratuit
More freeware about « Excel COUNTIF with 2 conditions »