Free Microsoft Excel 2013 Quick Reference

# SUMPRODUCT - Count Various criteria in same column (exclude other)

Hi

Iā€™m trying to count various items in a column, but not all. I can either do
this by stipulating a range of items or using a wildcard. However, Sumproduct
is not returning any results when I use more than one item/criteria for a
specified column.

Example:
=SUMPRODUCT((A2:A60000=ā€¯Debit Orderā€¯)*(B2:B60000=ā€¯Not Affordable due to*))
Or
=SUMPRODUCT((A2:A60000=ā€¯Debit Orderā€¯)*(B2:B60000=ā€¯Not Affordable due to
nett)*(B2:B60000=ā€¯Not Affordable due to gross))

Both results gives me a 0 result. I tried replacing the * with ,-- and , and +

Regards

EricB

## Related Results

### Count multiple criteria in two columns

Hi All

I'm struggling to work out how to count the volumes in two different colums using multiple criteria. I'm using the first column as a starting point with a singular criteria and then counting all entries in another column that conform to multiple criteria.

There's a screenshot below - what I want a formula to do is give the results in cells F5 and F10.

The criteria are in E5 and E10 respectively. E5 is saying that I want to select 'TREE' from the list in B3:B16 and then count how many times either 'MON' or 'TUE' occur in C3:C16. Likewise for E10.

Can anyone help me out with this formula?

Regards

Matt

### How to arange and count various numbers in previous column

Hi

I need help.

Let's say there is a one column with 9 cells. I need in another column to be aranged various numbers in previous column and in another column to be counted every number how many times appears.
Below is one small example of what I need.

4 2 2
8 4 3
10 8 2
4 10 1
8 15 1
15
2
2
4

### Counting different criteria in different columns ??

Hi
I need to count different criterias from different columns. I have been tryiing to work with array formulas but I just can't string it together. Also, in one of my columns some cells have only part of the criteria.

Column A - has either Australia Post , express, express post, Exp
Column B - has 24g, 24f, or 24t

I need a formula that will count all "Express" types (i.e express, express post and Exp)if it also has 24g in column B .

Hoping someone can help

snowee01

### Count Based On Criteria In 2 Columns

Need to count from a multiple column table based on criteria from two columns. Getting close with COUNTIF but struggling to properly insert the second criteria (nesting?).

First COUNTIF is based on column C (\$C\$7:\$C\$250) and references text value in "XXXX", which works fine. Need to also include second criteria, in same COUNTIF, from column I (\$I\$7:\$I:\$250) which would exclude counting if "CANCEL" was found in column I. Many thanks.

### Count and sum for criteria in multiple columns

Hello, I am trying to figure out how to create a formula using multiple criteria in different columns. Ideally, I need to use the whole column (i.e. E:E rather than E2:E400) because I don't want to have to update the formula every time I input data.

I will simplify my spreadsheet for example purpose. Basically, column A has a unique identifier that either begins with an "M" or an "R." Column B either contains a person's name or a "-". Column C contains a dollar amount.

1. I need to be able to count all the cells in Column A that begin with an "M" AND have a "-" in Column B.

2. I need to be able to SUM the \$ amounts in Column C ONLY for the items that begin with an "M" in Column A and have a "-" in Column B.

Is there any sort of formula that might do this? I have tried SUM arrays but as I said before, I would rather be able to use the whole column.

Any help would be much appreciated!!!

### Sumproduct with multiple criteria in single column

What if I wanted to have the criteria text in column C and wanted to sum the
values in Column B that matches the citeria specified in Column C in Column
A?
A B C
Salary 500 Salary
Bonus 400 Bonus
Fringe 300 Fringe
Travel 100
Entertainment 100

Someone suggested using the following, if I were specifying the criteria in
quotes. And this works.
=SUMPRODUCT(--(A1:A5={"Salary","Bonus","Fringe"})*(B1:B5))

But I want to reference Column C for the criteria. I tried doing
=SUMPRODUCT(--(A1:A5={C1,C2,C3})*(B1:B5))
but Excel returns an error message if I try this formula. Any help would be
much appreciated.

Thank you!

### Count Unique Occurrences in one column based on criteria another column

Hi

I want to do a count of unique occurrences of data in one column based on criteria in another column.

I have attached a sample worksheet with the data. I want to get the result in cell I26.

Thanks.

ltsolis

### Counting Different Names in A Column... Formula?

Just wanted to know if there is a Formula that can count DIFFERENT
names
in a Column?

eg.

There may be more than 1 same name, but I dont want it counted twice:

James
Edward
Max
John
David
James
James
Lisa

Total should = 6 (Since James is 1 person)

Is there a formula to solve this type of count? Thanks for any help
on this..

--
Mhz
------------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980

### Max Value in a column exclude selection - Excel VBA

Hi,

I used the below formula to find the max value in a column, and increment it
to the selection cell.

If Application.Count(Columns(1)) Then
MaxValue = Application.Max(Columns(1))
Else
MaxValue = 0
End If

If Application.CountA(Selection) = 0 Then
Selection.Value = MaxValue + 1
Else
If MsgBox("There are values in the selection. Are you sure you want to
replace?", vbQuestion + vbYesNo) = vbYes Then
' Check OK or Cancel, If OK, replace, if Not OK, abort
Selection.Value = MaxValue + 1
End If
End If

Example:
A1 has value 1
A2 has value 1
A3 has value 2

If I select A4, and click my macro button, it will put in value 3

My concern is like this:
If I select A3, it will prompt the mesg that if I want to replace it, then
if I click yes, it will put value 3
But I want it to put value 2, so that when find the max value, it actually
find all in the column , EXCLUDE those in the selection. How can I add those
checking in finding max value in a column excluding those in selection ?

I hope you got what I mean.

Thanks.

Regards.

### Count the text in a column - Part II

I would like to count the text in a column then for it to add a figure in
another cell if it meets the text criteria

Thanks!

### Vlookup with multiple criteria in same column

I have a single page of consolidated data that I need to pull from. The data is formatted like the following
Column A Column B
Category
Subcategory 1 Data
Subcategory 2 Data
Subcategory 3 Data
Subcategory 4 Data

All in the same column. I need a formula that will look for a certain category and then a certain subcategory and then provide the number to the right. The Categories and subcategories are in Column A and the data I want pulled over is in Column B

### Max Value in a column exclude selection - Excel VBA

Hi,

I used the below formula to find the max value in a column, and increment it
to the selection cell.

If Application.Count(Columns(1)) Then
MaxValue = Application.Max(Columns(1))
Else
MaxValue = 0
End If

If Application.CountA(Selection) = 0 Then
Selection.Value = MaxValue + 1
Else
If MsgBox("There are values in the selection. Are you sure you want to
replace?", vbQuestion + vbYesNo) = vbYes Then
' Check OK or Cancel, If OK, replace, if Not OK, abort
Selection.Value = MaxValue + 1
End If
End If

Example:
A1 has value 1
A2 has value 1
A3 has value 2

If I select A4, and click my macro button, it will put in value 3

My concern is like this:
If I select A3, it will prompt the mesg that if I want to replace it, then
if I click yes, it will put value 3
But I want it to put value 2, so that when find the max value, it actually
find all in the column , EXCLUDE those in the selection. How can I add those
checking in finding max value in a column excluding those in selection ?

I hope you got what I mean.

Thanks.

Regards.

### Calculate a count using info in two columns

I have been trying to use a formula to calculate a count using info in two columns on the same sheet i.e

IF a2:a252 = "1FBA" and b2:a252= "s" - to give the answer of how many ????

### Counting Different Names in A Column... Formula?

Just wanted to know if there is a Formula that can count DIFFERENT names
in a Column?

eg.

There may be more than 1 same name, but I dont want it counted twice:

James
Edward
Max
John
David
James
James
Lisa

Total should = 6 (Since James is 1 person)

Is there a formula to solve this type of count? Thanks for any help on this..

### Copy Rows That Match 2 Criteria in 2 Columns

Hi All, I am trying to get VBA to match criteria in two columns and when matched to copy and paste to new workbook. The Team date, "F" is contained in column I (team) and the date which should equal today's date is contained in column J (Date). Running the code on just the team works a treat, but the following code, it does not work at all. Any ideas please?

VB:

Range

Set myrange = Sheets("FFT").Range("I1", Range("I65536").End(xlUp))

Workbooks.Open ("Team F")

For Each c In myrange

If c.Value = "F" And c.Offset(0, 1).Value = Now() Then
i = i + 1
c.EntireRow.Copy Destination:=Sheets("FFT").Range("A65536").End(xlUp).Offset(1, 0)

End If
Next c
Workbooks("Team F").Save
Workbooks("Team F").Close
Workbooks("tester").Activate

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Regards
Oberonski

### Copying Columns with multiple criteria in different columns

I am using Excel 2003. I created an application using GUI that asks for input and then goes to a worksheet. All the information is kept on one worksheet. I have created several reports based on the criteria in certain columns. That copy and paste only pertinent information onto a new sheet. These reports work however I can not make the reports work using multiple criteria such as if column a = "Accepted" and column b = "Pass". I believe I am having issues with my loops. Here is an example of a report that works currently but with only one criteria.

VB:

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Sheet10.Visible = xlSheetVisible

Dim temp As String

Sheet10.Activate
Dim ocell As Range
Dim bnone As Boolean
bnone = True
Dim current_row As Integer

For Each ocell In ActiveSheet.Columns(1).Cells
If IsEmpty(ocell) Then
ocell.Select
'MsgBox ("found at " & oCell.Row)
bnone = False
Exit For
End If
Next
current_row = ocell.Row

Dim blah As String
Dim blah1 As String

Sheet3.Activate

For Each ocell In ActiveSheet.Columns("DD").Cells
If Trim(ocell.Text)  "" Then

blah = "CS" & Trim(Str(ocell.Row))
blah1 = "G" & Trim(Str(current_row))

Sheet3.Activate
temp = ("Onboard")
Sheet10.Activate
Sheets("Daily Snapshot").Range(blah1).Value = temp

Sheet10.Visible = xlSheetVisible

blah = "A" & Trim(Str(ocell.Row))
blah1 = "A" & Trim(Str(current_row))
Sheet3.Activate
temp = Range(blah).Value
Sheet10.Activate
Sheets("Daily Snapshot").Range(blah1).Value = temp

blah = "B" & Trim(Str(ocell.Row))
blah1 = "B" & Trim(Str(current_row))
Sheet3.Activate
temp = Range(blah).Value
Sheet10.Activate
Sheets("Daily Snapshot").Range(blah1).Value = temp

blah = "I" & Trim(Str(ocell.Row))
blah1 = "C" & Trim(Str(current_row))
Sheet3.Activate
temp = Range(blah).Value
Sheet10.Activate
Sheets("Daily Snapshot").Range(blah1).Value = temp

blah = "J" & Trim(Str(ocell.Row))
blah1 = "D" & Trim(Str(current_row))
Sheet3.Activate
temp = Range(blah).Value
Sheet10.Activate
Sheets("Daily Snapshot").Range(blah1).Value = temp

blah = "K" & Trim(Str(ocell.Row))
blah1 = "E" & Trim(Str(current_row))
Sheet3.Activate
temp = Range(blah).Value
Sheet10.Activate
Sheets("Daily Snapshot").Range(blah1).Value = temp

blah = "BY" & Trim(Str(ocell.Row))
blah1 = "F" & Trim(Str(current_row))
Sheet3.Activate
temp = Range(blah).Value
Sheet10.Activate
Sheets("Daily Snapshot").Range(blah1).Value = temp

current_row = current_row + 1

End If

If ocell.Row = 2000 Then
bnone = False

Exit For
End If

Next

End Sub

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
(in this report everyone who has a blank in column DD is printed on the worksheet Daily Snapshot(report). Certain information is carried over onto this sheet as well as the status Onboard.)Sheet 10 is the report worksheet and sheet 3 is the main worksheet titled "application".

I have tried putting another for loop around this for loop to add a criteria such as
For Each ocell In ActiveSheet.Columns("A").Cells
If Trim(ocell.Text) = "Accepted" Then...

but it does not work.

Any help is much appreciated!! Thanks!!

### Delete Rows based on criteria in two columns - Help Please !

I have been struggling with a macro for my excel worksheet. I need a macro that will delete rows based on criteria in two columns. Specifically....

EXAMPLE (note row 1 is column headings)

ITEM CODE = COLUMN A
DESC = B
LOCATION = C
STATUS = D

I need to delete all rows in which the value of column C = "ASSEM"
I ALSO need to delete all rows in which the value of column D = "D" or "M"

I would really appreciate any help, thx.

### Need to find matching criteria in 1 column, then add amounts in a

Hi,
Trying to match criteria in one column and then add amounts that are in a
second column. Need to find all in column A that match, then add amounts in
column B for matching criteria. Thoughts? Thinking maybe better to try in
Access?
A B
1 8268001100 144.78
2 8268001100 12133.09
3 8266012100 640.84
4 8266012100 404.11
5 8206011600 122.14
6 8206011600 1787.00

### Count Unique Values in 1 Column based on Date Range in another Column

Howdy All,

I'm use the CountU function to count unique values in a column and it works
great!

Now what I want to do is count the unique values in Column B which occur
between a specific date range in Column A.

Column A contains dates from 2005 through 2009.

Column B contains Order Numbers for each date.

I want to count the unique values in column B for only 2008.

Any Ideas?

Thanks,
Brian

### Counting Duplicate Names in a Column

Greetings and Salutations,

I am trying to count unique names in a column filled with duplicate names. I would like to return a numerical value...Please see attached file.

### Count blank cells in a column, depending on a value in another row.

I posted this earlier, but my post disappeared somehow.
anywho, i would really appreciate any help because this is driving me nuts.
im using excel 2003:

Here is a screen grab of what i am trying to do:

http://skitch.com/lukehall/bey27/picture-1

i want to count blank cells in a column, depending on a value in another row.

B10 should count the blank cells which have "M" in column A (2)
B11 should count the blank cells which have "F" in column A (0)
C10 should count the blank cells which have "M" in column A (1)
C11 should count the blank cells which have "F" in column A (1)

Thanks guys,

### Count duplicate combinations in 2 columns

Currently I'm using the following function to count unique combinations in 2 columns and that works fine. But how can I adapt this function to count duplicate combinations instead of unique combinations?

Function UniqueCount(searchFor
As Variant, searchColumn As Range, countUnique As Range) As Variant
Dim noDupes As New Collection
Dim c As Range, myCount As Long
Dim strSearch As String, strFound As String

On Error Resume Next
'if user entered a range, read its value otherwise, accept it as a string
Set c = Application.Intersect(searchFor, searchFor.Parent.Cells)
If Err Or c Is Nothing Then
Err.Clear
strSearch = searchFor
Else
strSearch = searchFor.Cells(1).Value
End If

'check the column references
On Error GoTo referenceError
If searchColumn.Cells.Count <> countUnique.Cells.Count Then
'the search range and count range do not match
GoTo referenceError
End If

'look at each cell in the search column
On Error GoTo unknownError
myCount = 0
For i = 1 To searchColumn.Cells.Count
'does this cell value match what we're looking for?
If searchColumn.Cells(i).Text = strSearch Then
'check the corresponding cell in the count column
On Error Resume Next
strFound = countUnique.Cells(i)
'attempt to add this item to the collection
If Err Then
'it was a duplicate, ignore it
Err.Clear
Else
'it was unique, count it
myCount = myCount + 1
End If
End If
Next i

'return the count of unique items
UniqueCount = noDupes.Count
GoTo leave

referenceError:
'return a reference error
UniqueCount = CVErr(xlErrRef)
unknownError:
'untrapped error; return "#NA"
UniqueCount = CVErr(xlErrNA)
leave:
End Function

### Count unique values in a column

Hello

Can anyone help me with this? i want a simple vba code to count unique values in a column(text as well as numbers).

thanks in advance for any help!

### Counting any data in a column

Hello everyone,

Hope all is well.

Is there a formula that I can put into a cell that will look at say Column H. and then it will count any cells in that column that contain any value at all (just not a blank cell) and total them?