Excel Formula Help

androo

androo

androo

Associate
Joined
8 Mar 2003
Posts
652
Hi,

Im trying to work out a formula which is to look through three colums of data to sum only the data which matches the specified category.

I have a Primary category of 1, 2, or 3.( Column L) Under these categories, each item has a Secondary Category of 1 through to 20 (Column G). Each of these then has a Sub Category of 1 through to 99 (Column H).

The formula I have at the moment is trying to find how many items have a Primary Cat of 1, with a Secondary Cat of 1 and a Sub Cat of 1. Im only interested in those items with a Primary Cat of 1.(So I will always want to disregard Primary Categories 2 & 3 - but without having to go through all the data)

This is what I have so far:

=SUM(IF($L$2:$L$50="1",IF($G$2:$G$50="1",IF($H$2:$H$50="1",1,1),0)))

At present, all this returns is 0 - not a total of how many items are categorised as 1/1/1.

There is probably a shorter way to get to the question Im asking, but any ideas why its not working?
 
I think, sumproduct can help you.

Example:
=SUMPRODUCT(--(A1:A10="TEST")*(B1:B10=1)*(C1:C10<=5))

Try that, but obviously tailored to your needs.
 
If you're comfortable with the VB editor then I would suggest creating your own function for this rather than mess about with Excel's limited built in stuff.

Simply open the VB editor, insert a new module by right clicking on the left hand pane and paste in this code

Code: 
Public Function CountMatchingRows(ByVal RangeToCount As Range, ByVal NumberToMatch As Long) As Long

  Dim i As Long
  Dim j As Long
  Dim lngCounter As Long: lngCounter = 0
  Dim blnMatched As Boolean
  
  
  For i = 1 To RangeToCount.Rows.Count
    blnMatched = True
    For j = 1 To RangeToCount.Columns.Count
      If RangeToCount.Cells(i, j) <> NumberToMatch Then blnMatched = False
    Next
    If blnMatched Then lngCounter = lngCounter + 1
  Next
  
  CountMatchingRows = lngCounter
  
End Function

Now you can use this new function in the following way from within your worksheet:

=CountMatchingRows(A1:Z100,1) where A1:Z100 is your chosen range and 1 is the number against which you are matching.
For each row in your range whose values are all equal to your chosen number the count will be incremented.

Beats Excel's own formulae any day ;)
 
You must log in or register to reply here.
Back
Top Bottom