# COUNTIFS On Filtered Table To Find Sum For A Column Based On 2 Criteria

May 23, 2013

I'm trying to use countifs on a filtered table to find the sum for a column based on 2 criteria.

I've used =SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet3!K7,ROW(Sheet3!K7:Sheet3!K7:K20000)-ROW(Sheet3!K7),0)),--(Sheet3!K7:Sheet3!K20000="Yes"))

for a single criteria which is great, but I also need it to look at column B.

In a non-filtered table it works as:

=COUNTIFS('Sheet3 '!\$K:\$K,"Yes",'Sheet3 '!\$B:\$B,B5)

Is there any way to do this?

## Find A Value In A Table Based On Other Criteria

May 30, 2009

I'm trying to find a suitable formula that looks at two cells (J3 and V3) in Sheet 1 (a person's employment grade (e.g. 18) and their performance percentage e.g. 92.5%), finds those values in a table on Sheet 2 and then places the corresponding value from that table in Sheet 1 at cell Z3.

## CountIfs With Between Dates And Two Extra Criteria That Excludes Blank Cells In Another Column

Aug 3, 2013

I have been using CountIfs with a lot of luck until I added one extra criteria that it ignores.

ORIGINAL FORMULA
=COUNTIFS(ADate, ">" & N\$2, ADate, "=" & 10000, WBid, "" & N\$2, ADate, "=" & 10000, WBid, "

## Find Value In Table Based On Value In Row & Column

Mar 11, 2008

I have a table where the columns are named: A, B, C, D and E and the lines are named: 1,2,3,4 and 5. All the cells of this table are filled with values.
I want the function to return the value on the correspondent cell everytime I type a combination of column and line. For instance if I type "B2" the function would return the value on this position...

Actually I am looking for something like a combination of the functions "hlookup" and "vlookup"...

## Pivot Table - Use Same Column To Get Criteria Based On Different Filters

Aug 21, 2012

I'm trying to make a pivot table that can compare sales based on the whatever month/year/salesman combination I give.

Ideally, I'll be comparing the sales data for 3 seperate months. Can I make a pivot table where I can make different filters apply to specific columns?

## Find Max Or Highest Time In Column G Based On Criteria?

Mar 20, 2014

Im looking to find the max or highest time in column G based on the criteria (date selected and name) So if the date is the 10/02/2014 in G10 it would look up that date and the name and bring back the time in column H. I have put an example answer in H12.

## Copy Data Table Seperate Sheets Based On Column Criteria

May 12, 2008

I need to copy all the work sheets into one single work sheet (mastersheet). The source work sheets are having same column structure. The condition which i need to take care of is that after column 3 if at all there is any data till column 10 then in the destination mastersheet these should be copied in different rows with first two columns repeated. I need to do this using VBA macro.

## Can Add Spare Row Between Column Headings And Data In Filtered Table

May 2, 2014

I have a table of data with column headings. I'd like to be able to filter the table based on the column headings, however I have a 'spare row' between the column headings and the data which I use to add new entries to the table using a macro (the macro copies the row and pastes it in one of the empty rows at the bottom of the table, then sorts the data which places it with the rest of the data).

The problem is, Excel won't let me apply a filter to the table if the column headings are separated from the data that I would like filtered.

## Count Unique Values Based On Duplicate Status In Another Column On Filtered Data

Mar 5, 2014

I am able to quite easily count the number of specific values in a cell after the table has been filtered. However, the problem I have run into is that some times the data needs to be placed into the spreadsheet twice (or to be more specific the same subject is associated with several unique data points).

What I need: some way to count the instance of some give value in column D only once based on the presence of a duplicate (unique) identifier in column C. However, when I filter the entire database, it must count *only* the filtered cells and not the hidden cells as well.

Picture:
Column C Column D
111111 M
111111 M
111111 M

[Code]....

Currently calculates: M=9, F=2

Right now it incorrectly states there are 9 "M" from column D when it really should be 5 since 3 are duplicate values. My main difficulty is making sure this continues to work after I filter the entire sheet (say column ZZ) and have a bunch of hidden cells.

Equation currently using to count only filtered values (in this case "males" and "females"):
="M = " & SUMPRODUCT(SUBTOTAL(3,OFFSET(D3:D13,ROW(D3:D13)-MIN(ROW(D3:D13)),,1))*(D3:D13="M"))

## COUNTIFS With Many Criteria In Same Range

Feb 5, 2014

How to combine many criteria in a COUNTIFS formula with the same range.

=COUNTIFS(brut!\$B:\$B;"1T";brut!\$G:\$G;"F25"; brut!\$C:\$C;"1")

And it works fine, but I need four more conditions -

brut!\$C\$C; "2", brut!\$C\$C; "3", brut!\$C\$C; "4" and brut!\$C\$C; "5".

When I change the formula to

=COUNTIFS(brut!\$B:\$B;"1T";brut!\$G:\$G;"F25"; brut!\$C:\$C;"1"; brut!\$C\$C; "2"; brut!\$C\$C; "3"; brut!\$C\$C; "4"; brut!\$C\$C; "5")

It didn't work anymore.

I don't know how to use IF and COUNTIFS together.

## Countifs With References As Criteria

Aug 13, 2009

I'm trying to use this formula in a cell but it keeps giving me #NAME.

What does this mean?

## Month Criteria In Countifs ????

Feb 11, 2010

check out the COUNTIFS function on Data Tables Sheet -> Cell D29 and pls. let me know what's going on. I need is : To calculate the number of items on system data tab which has the "Pending" status and items which belongs to month "Jan-10" under a particular person selected from B 27 cell (Data tables tab).

I solved it by using >=01-01-10 and <=31-01-10 criteria but i don't think its nice idea .... I tried to use AND(function by got failed ...........

## Countifs Using Two Criteria Of Which One Is Colour?

Jul 17, 2014

I need to count certain data if they match the same week number but they are not highlighted in red (manual colour not conditional formating). I have a macro that allows me to count cells with a certain colour but I cannot seem to tell it to only to count them if it also meets the criteria of relating to the same week.

Can i somehow combine these to functions or do I need to add soemthing to the countifcolour macro?

## Countifs Multiple Criteria Array?

May 20, 2014

I would like to count the number of values in column B IF the value in Column A is equal to a value from a range of cells.

I would like the formula to be in cell E7. And the range of cells containing the values is in column D.

Here is my workbook Book1.xlsx

## Add / Remove Criteria In COUNTIFS Statement

Apr 14, 2014

how to add/remove criteria to a COUNTIFS statement, based on the value of a VBA UserForm Checkbox.

All of the criteria for the checkboxes are based out of the same column. Here is the similar code I currently utilize for my checkbox filter.

[Code] ..........

The code hides fields within a collumn based on the tag within the checkbox's properties. Now I am trying to create an addition that will add criteria to a CountIf formula in real time.

## Criteria Values In COUNTIFS Function

Dec 2, 2008

I have the following formula in a sheet to record basketball stats that I am putting together.

=COUNTIFS('Running Game Log'!A:A,"=Jarod",'Running Game Log'!C:C,"=2 pt miss")

This formula counts from a list that is generated during in-game recording of stats & obviously counts the number of times "Jarod" misses a "2 pt shot".

My question is can the criteria "Jarod" refer to a value in a cell, rather than the specific name "Jarod" as I want to be able to change player names easily, rather than have to edit this formula every time.

## CountIfs Formula Where One Criteria Is String Within Cell

May 10, 2014

I know this should be straightforward, but just can't "hack the logic", whether using "Countifs" oor "SumProduct" formulae.

Attached file 140509 String Criteria.xlsx file has database of three fields (Date, Route number and Part number)

I need the formula that totals number of rows meeting three criteria:
(a) A specific date from Col A
(b) A specific number from Col B
And the part I can't sort out:
(c) Any cell containing a "string" in Col C (so needs to count the "core" even if it has a prefix or suffix).

E.g. How many rows have "Date x" in Col A, "Route Y" in Col B and a cell containing "123" in Col C?

## COUNTIFS Using Date As Criteria With Wild Card

Mar 31, 2014

I'm trying to use the COUNTIFS function & have run into trouble because the 2nd criteria is a date. Typically I would just set the range & then put "01/01/2014" if I was looking for all of the January 1, 2014 cells. However in this spreadsheet my source data contains the date with the time. (01/01/2014 11:27:39 AM). Becasue the time is included in the cell, the function returns no results when I set teh date.

I tried to use a wild card "01/01/2014*" which I thought would bypass the time but it still retuns no results. Is there a way to use a windcard with a date cell?

## Using Multiple Criteria In Second Argument Of CountIfs Function

Feb 26, 2014

Need to use CountIFs formula to evaluate multiple conditions in the same column. Here is the formula I tried. It works for Resolved but returns 0 when i try to add Duplicate.

=COUNTIFS('P12 Source'!H:H,A18,'P12 Source'!F:F,"Resolved,duplicate")

Also used this formula with success by referencing a cell that had "Resolved" in it but when I tried to add another cell with "Duplicate" it again returned 0.

=COUNTIFS('P12 Source'!\$H:\$H,A19,'P12 Source'!\$F:\$F,'P12 Source'!\$F\$75)

## COUNTIFS Formula Identifies Alphanumeric And Date Criteria

Mar 15, 2013

The attached file Countifs issue.xlsx shows my attempt to construct a "CountIfs" formula that finds the one record out of ten that matches the following criteria:

a) Col A has 10 IDs. 3 of them are between 40 - 49, and another 2 are alphanumeric. This formula SHOULD identify these 5:

=COUNTIFS(A2:A11,">39",A2:A11,"<50",A2:A11,"4AB",A2:A11,"40C")

Doesn't work, It finds the 3 numerics but returns "0" when the two alpha-numerics are added.

b) Col B has a list of dates. 5 of the 10 are this month. This formula SHOULD identify them:

=COUNTIFS(B2:B11,(EOMONTH(NOW(),-1)+1),B2:B11,(EOMONTH(TODAY(),0)))

Doesn't work. It returns a value of "0".

c) Col C shows the Status for each record. 5 are "A" and 5 are "B". The following formula DOES find all the "B"s

=COUNTIF(C2:C11,"B")

But obviously I need the full formula to find the one record out of the ten that meets all the criteria.

in (a) and (b) and enable me to combine all three sections.

## Populate Some Results Using Countifs Formula With Multiple Criteria?

Feb 16, 2014

I am trying to populate some results using countifs formula with multiple criteria. below is the formula am using and the last two criterias are dates

=COUNTIFS('Unproccessed CIT'!\$B\$5:\$B\$411,Summary!\$A\$4:\$A\$35,'Unproccessed CIT'!\$G\$11:\$G\$417,"<>"&"closed",'Unproccessed CIT'!\$A\$5:\$A\$38,">="&\$C\$2,'Unproccessed CIT'!\$A\$5:\$A\$38,"<="&Summary!\$E\$2)

Results:
#VALUE!

## Countifs With 2 Date Range In Separate Table

Mar 4, 2014

How can i count "how many "A" for period date in table and date out table in this case"

Date inDate outresult Count
05-Mar08-MarA ?
05-Mar09-MarB ?
06-Mar07-MarA ?
07-Mar08-MarB ?
07-Mar08-MarB ?
08-Mar09-MarC ?
05-Mar08-MarA ?
05-Mar08-MarA ?

## Find And Sum Based On Criteria

Feb 26, 2009

I have a sheet with 3 columns - Line, Product and amount. I have to calculat the amount based on which line it is, and which product.

I. e I want to calculate all the amount in Line 1 except if the product is 3,23,31 (just an example - the range can be longer).

In workbook added i have shown the problem. I have tried the sumproduct formula, but cant get it right!

## Sum Range Based On 1 Criteria Of Column & 2 Criteria Of Another

Mar 4, 2008

i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)

I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}

I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message

Function
Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)

Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)

-Didnt get thru this bit to start building on the Function! keep giving me #Value!

## Find The Nth Largest Value Based On One Criteria

Mar 9, 2009

I have a data set where I am trying to find the nth largest value based on one criteria? How would one use do this using the large function?

Example:

Dept. Name / Widgets
ABC / 10
ABC / 8
XYZ / 9
ABC 7
ABC / 3
XYZ / 4

If I need the third most widget production of Dept ABC, the formula return 7.

## Find Max Date Based On Criteria

Mar 20, 2012

I have a table of shipments made to my company broken down by style, color, and delivery date. I need to find the most recent delivery date for each style/color combination.

This is my table
ABCStyleColorDelivery Date1ABCRed1/1/20122ABC
Blue2/15/20123123Red3/12/20124123Blue1/30/20125ABC
Red2/27/20126ABCRed3/1/20127123Blue3/20/2012

and my desired results are as follows:
StyleColorMost Recent DeliveryABCRed3/1/2012ABCBlue2/15/2012123Red3/12/2012123Blue3/20/2012

The style and color columns are already filled in, I just need a formula for the date column.

## Find Last Sheet Based On Criteria

Dec 16, 2013

I have a workbook with a number of sheets, the first 5 are data sheets populating the other 30 (more will be added in the future).

These worksheets are renamed with a macro when the data is refreshed, allowing the sheets being used to vary week on week

The code i currently have running does a number of things, however i need it to run only for the relevant worksheets

The sheet names are either 4 digits long, or 1 digit if unused.

I need a bit of code that says run my macro for all sheets with a name of 4 characters and ignore the rest.

## Pivot Table Criteria Based On Cell Value?

Jan 27, 2014

filter my pivot table using a cell value, rather than manually selecting from the dropdown.

The report filter title is Rnd H, and values start from 0 through to 2, at .1 intervals (so 0.1, 0.2, 0.3, 0.4 etc).

Rather than select "0.3" manually from the dropdown, can the pivot table do this if "0.3" was written in cell A1 for instance?

## Move Rows In A Table Based Upon Certain Criteria

Jan 3, 2014

I have four sheets with the same size tables and just need rows to be able to be moved back and forth based on input from a certain cell and just added to the next available blank cell in the desired table. Adding the code from the webpage below works very well but only puts into the worksheet and not into the actual table. Is there any way making it actually input the row into the table itself?

Creating Macro that automatically moves row to another spreadsheet?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
If UCase(Target.Value) = "COMPLETE" Then
Target.EntireRow.Copy Destination:=Sheets("Archive"). _
Range("A" & Rows.Count).End(xlUp).Offset(1)
Target.EntireRow.Delete
End If
End If
End Sub

## Fill In Table Based On Multiple Criteria

Jul 11, 2007

I would like to fill in a table based on a small database
this table then creates a graph.

But I need to look at 3 criteria before I can fill in the table
namely: customer - weeknumber - weekday

based on these items the time should be filled in accordingly.

Can this be done with regular formula or should I try VBA ?

please have a look at my file the data should go from sheet input to sheet ACL
based on the above mentioned criteria