Free Microsoft Excel 2013 Quick Reference

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 +

Any advice?

Regards

EricB

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 +

Any advice?

Regards

EricB

- Count multiple criteria in two columns
- How to arange and count various numbers in previous column
- Counting different criteria in different columns ??
- Count Based On Criteria In 2 Columns
- Count and sum for criteria in multiple columns
- Sumproduct with multiple criteria in single column
- Count Unique Occurrences in one column based on criteria another column
- Counting Different Names in A Column... Formula?
- Max Value in a column exclude selection - Excel VBA
- Count the text in a column - Part II
- Vlookup with multiple criteria in same column
- Max Value in a column exclude selection - Excel VBA
- Calculate a count using info in two columns
- Counting Different Names in A Column... Formula?
- Copy Rows That Match 2 Criteria in 2 Columns
- Copying Columns with multiple criteria in different columns
- Delete Rows based on criteria in two columns - Help Please !
- Need to find matching criteria in 1 column, then add amounts in a
- Count Unique Values in 1 Column based on Date Range in another Column
- Counting Duplicate Names in a Column
- Count blank cells in a column, depending on a value in another row.
- Count duplicate combinations in 2 columns
- Count unique values in a column
- Counting any data in a column

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

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

Thanks in advance.

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

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.

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!!!

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!

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

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

View this thread: http://www.excelforum.com/showthread...hreadid=571093

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.

another cell if it meets the text criteria

Thanks!

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

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.

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

Can someone please help me? I would be so grateful

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..

VB:RegardsRange 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").ActivateIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Oberonski

VB:(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".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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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!!

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.

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

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

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.

Thanks in advance!

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,

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 noDupes.Add strFound, strFound 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

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!

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?

Thank you all in advance!

No luck finding an answer? You could always try Google.