Function deciphering...

**EXCEL40294** · 06-09-2015, 02:47 PM

Hey guys, my name is John.

I had a quick questions regarding a formula that I've run across. I would like to know, piece by piece, what each section of this formula represents. I've already done some research on it...but I would like to read up on how it would apply to my specific situation...here is a part of it:

=SUM(D13:F13)-COUNTIFS(SHEET1!$A:$A,SHEET2!$A$12,SHEET1!$B:$B,SHEET2!$D$1,SHEET1!$K:$K,SHEET2!$A$2,ZBD!$C:$C,"E")

So, as an example of what I'm asking for is this..

=SUM(D13:F13) ...means you want the sum of all data within this range/block of cells

-COUNTIFS(SHEET1! ...means you want to subtract a certain value from the "SHEET1" tab

etc....etc....

Thank you very much for the help!

I am a first time user of this forum and my questions are very likely simple compared to what usually makes it on these posts...

I appreciate any help I can get! Thanks!

**6StringJazzer** · 06-09-2015, 03:06 PM

I think you would learn a lot by using Excel Help or just doing a web search on the COUNTIFS function.

This function counts the number of values range, including only those values in where a set of conditions is met.

Syntax

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

The COUNTIFS function syntax has the following arguments:

criteria_range1 Required. The first range in which to evaluate the associated criteria.

criteria1 Required. The criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32".

criteria_range2, criteria2, ... Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

IMPORTANT Each additional range must have the same number of rows and columns as the criteria_range1 argument. The ranges do not have to be adjacent to each other.

In your formula, it is counting the number of rows where all these conditions are true (I didn't include the sheet reference but that part is obvious):

The value in column A match the value in A12
The value in column B matches the value in D1
The value in column K matches the value in A2
The value in column C is "E"

**AstToTheRegionalMGR** · 06-09-2015, 03:06 PM

=SUM(D13:F13)-COUNTIFS(SHEET1!$A:$A,SHEET2!$A$12,SHEET1!$B:$B,SHEET2!$D$1,SHEET1!$K:$K,SHEET2!$A$2,ZBD!$C:$C,"E")

First step: SUM(D13:F13): The formula gets a sum of D13, E13, F13.

Second: COUNTIFS is a formula that counts the number of cells with a range that meet certain criteria, so what it returns is frequency that the criteria are met within the range. How it works is that you first name a range, then you name the criteria to be met within this range. you can then write a second range (or the same range), followed by a criteria for that second range (or a second criteria to be met within that same range). So in this case, the COUNTIFS is broken down as follows:

Range 1: Sheet1!$A:$A (this means the entire column A in sheet1)
Criteria to be met in Range 1: The value found in SHEET2!$A$12 (so you could just look at cell A12 in Sheet2 to find out what the criteria is)

Range 2: SHEET1!$B:$B (all of column B within sheet1)
Criteria 2: SHEET2!$D$1 : Similar to criteria 1, this is just the value found in Sheet2 in cell D1

Range 3: SHEET1$K:$K : All of Column K in Sheet1
Criteria 3: SHEET2!$A$2 : The value found in Sheet2 in cell A2

Range 4: ZBD!$C:$C : The entire column C within the sheet called ZBD
Criteria 4: "E" : This just means it will search for any cell within range 4 that just says "E"

Third step: The last thing this formula does is subtract the number of times ALL the criteria were met from the COUNTIFS formula from the SUM of D13:F13

Hope that's helpful!

**nigelbloomy** · 06-09-2015, 03:14 PM

SUM adds up the values in D13 to F13.
Countifs can have multiple conditions. Usually it is used to filter several levels into one data set. First you select the range you want to count (it will just count how many cells have values in them), then you select the criteria.
So for your example count if going to look at cunting every cell in column A of sheet 1. Then it will only count those that match the value in sheet 2 cell A12.
The strange part is that your formula doesn't appear to be sorting one dataset. The first part will take the whole column and return the list of rows that matches A12. The next part will filter down those rows further by only showing rows that also match D1 on your sheet 2, that will further be filtered down to show rows that also match A2 on sheet2. Once all of the filters have been applied, countif will then count how many rows match all of those criteria.
Like I said though, usually this is used to apply several filters to one dataset. So if you have sales areas you can filter down to only count one specific sales area, then you can further filter down to count only one sales person in that area.