Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

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 +

Any advice?

Regards

EricB


Post your answer or comment

comments powered by Disqus
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

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

Thanks in advance.

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

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.

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

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!

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

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
View this thread: http://www.excelforum.com/showthread...hreadid=571093

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.

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!

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

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.

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

Can someone please help me? I would be so grateful

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

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

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

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.

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

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

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.

Thanks in advance!

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,

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


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!

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?

Thank you all in advance!


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