Excel Formula Efficiency 4: SUMIFS & COUNTIFS Are Fast

I am working on a data base where I am trying to count occurances for multiple variables. In my DB I am trying to calculate the number of employees who quit work in a specific department during a specific date range.

Column w contains the department, I want count for dept "SB"
Column f contains the termination date, I am comparing the termination date in column F on the DB to cell a1 and a2 on a different worksheet, a1 for example has the date 1/1/2009, a2 has the date 1/8/2009, so I am looking for terminations that occurred between 1/1/2009 and 1/8/2009 (between a1 & a2).

The other criteria a have is a column M in the datbase that contains the reason the person terminated. I am looking to see if there is data in the cell to determine if the person terminated.

So I want to count the # of people in department SB(column W23:w3500, who have a termination reason in column M23:m3500,whose termination date is in the date range A1:a2 (1/1/2009 - 1/8/2009).

Can anyone help me?

Hi, below is the current excel formula.
Is there any limitations with Sumifs and Countifs with or combinations
'=SUM(COUNTIFS(B2:B27,"May",C2:C27,{"FEC";"ORANGE"},A2:A27
,{"NY","GA","LA"}))

for exampel if I want it this way {"NY","GA","LA","AZ","TX"})), will it still give me accurate answer?


Thanks in advance.

Hi,

I am trying to consolidate a report in which I need to get the sum & count for couple of rows.

Col A = Country Code
Col B = Table
Col C = Field Name
Col D = Field 1
Col E = Message
Col F = Desc

Country
Table
Field Name
Field 1
Message
Desc
SumChk
10



Table 1
AIRC
No rule found
Rule Error
3



12



Table 2
BOCJ
DIDW
Data Type miss-match
Data Error
1



14



Table 1
AIRC
2



No rule found
Rule Error
1



10



Table 3
BOCJ
3



No rule found
Rule Error
1



12



Table 1
AIRC
Data Type miss-match
Data Error
6






I want to get the no. of records for the following criterias.

Sum of Col G (SumChk) - Country = 10 & 12 with Table = Table 1 & Table 2 with Field 1 = Blank and Desc = Rule Error & Data Error

Result should be 9.



Count of Col G (SumChk) - Country = 10 & 12 with Table = Table 1 & Table 2 with Field 1 = Blank and Desc = Rule Error & Data Error

Result should be 2.


Any help is appreciated.

Thanks in advance.

what is sumifs and countifs function

Can you use a cell reference with the SUMIFS and COUNTIFS formulas and Defined Names?

To clarify, I'm wondering if you can do this the following.

Create a Defined Name of SalesRegion (Northeast, Southeast...etc.) & Sales (SalesNumber1....SalesNumbern).

I know you can =SUMIFS(Sales, SalesRegion, "="&"Southeast")

If you had the words "SalesRegion", "Sales" in cells A1 and A2, respectively, could I do this? (I know you can put "Southeast" in a cell A3 and it works.)

=SUMIFS(A2,A1,"="&A3)

If I can do this, what am I doing wrong to not achieve the correct result?

I have the following:

a b c d e f g 5 112 34 6 666 667 123
I want search the second row for the largest value then display the value in the first row, so it would be f. But what if there are multiple values in the second row that have the same largest value? is it possible to display say if e and f were 667, to display E,F in one cell?

The AVERAGEIFS function returns the average (arithmetic mean) of all cells that meet multiple criteria.
The SUMIFS function adds the cells in a range that meet multiple criteria.
The COUNTIFS function counts the number of cells within a range that meet multiple criteria.



The AVERAGEIFS function in cell A8 returns the result of 12.5
1.First Argument: The Averaging range, 12.5 is an average of 10+15 appearing in that range.
2.Second & Third Argument boxes: Criteria range 1 + Criteria1, 10, 15 & 20 meeting the criterion ">2" in List range.
3.Fourth & Five Argument boxes: Criteria range 2 + Criteria2, 10 & 15 meeting the criteria ">5" in Number range and ">2" in List range.



The SUMIFS function in cell A9 returns the result of 25
1.First Argument: The Sum range, 25 is the sum of 10+15 appearing in that range.
2.Second & Third Argument boxes: Criteria range 1 + Criteria1, 10, 15 & 20 meeting the criterion ">2" in List range.
3.Fourth & Five Argument boxes: Criteria range 2 + Criteria2, 10 & 15 meeting the criteria ">5" in Number range and ">2" in List range.



The COUNTIFS function in cell A10 returns the result of 2
First & Second Argument boxes:
Criteria range1 + Criteria1, 2 is the number of cells meeting two criteria in List range.
Third & Fourth Argument boxes:
Criteria range 2 + Criteria2, 10 and 15 meeting the criterion ">5" in Number range.

Hi All

Am trying to set up a larger spreadsheet as attached. I would like fomulas in Sheet1 b2:c4
which will populate the fields based on the data on sheet 2. eg cell b2, F1 is filtered by DUB so this counts the amount of times DUB apeears on sheet 2 broken down by 1 for FP ABS, twice for ROH EQ and once for ROH SW. Have been playing around with SUMIFS all evening but can't seem to get it right, would appreciate any help

thanks

Noel

Hello everyone!

I have a spreadsheet that has 10 columns. I'm trying to use the countifs or sumifs formula to search two columns to find a matches and then add the number from another column from the match it found.

Ex.
-------Col1 -- Col2 -- Col3--Col4
Row1--1------help----500--Active
Row2--2------move---600--Deleted
Row3--3------done---500--Active
Row4--3------home---500--Deleted

I need to search column3 and column4 for the number 500 in col3, search for Active in col4 and then when it finds the 500's and Active's look in column1 and add the numbers. So my result should be 4.

Anyone have any ideas?
Thanks
Shane