# Sums and CountIfs

• There is this huge file I have and I want to count the items in column A for each location (identified by a number in the reference sheet). Why aren't my sums working (in the first two of column B) or do I have to use CountIfs? If so, how would I phrase the countif with two conditions?

HarriBess

## Files

• Re: Sums and CountIfs

Please make sure there are no external references. In addition there are circular references you may want to correct first.

Regards,

Wigi

Excel MVP 2011-2014

For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

-- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

• Re: Sums and CountIfs

I don't know what that means, Wigi..

• Re: Sums and CountIfs

Quote from Wigi

Please make sure there are no external references. In addition there are circular references you may want to correct first.

When I opened your file, there were links to other sheets on your PC. Obviously, we here don't have these files so we cannot see what is happening.

Circular references mean that some cell x refers to another cell y AND y refers to x. Excel cannot calculate the result.

Wigi

Regards,

Wigi

Excel MVP 2011-2014

For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

-- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

• Re: Sums and CountIfs

Okay. I think I have my workbook page set up for easier viewing. I just pasted the values.

What I want to do is look at data on another sheet and summarize by counting the number of occurances of that Rotor/Stator for the Location, which are all assigned numbers on that some sheet.

Is this better?

I just can't figure out which formula to use nor how to phrase it.

## Files

• Re: Sums and CountIfs

I think I will need to do an IF and then qualify the CountIf??
The two conditions are what's making this so difficult.

• Re: Sums and CountIfs

Harri

see attachment for an example of the SUMPRODUCT function. That will do what you want. Adapt to your setting.

Wigi

Regards,

Wigi

Excel MVP 2011-2014

For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

-- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

• Re: Sums and CountIfs

Okay. So I tried the SUMPRODUCT, it's giving me zeros when I can look at the data and count more than that. Here's what I've typed:

=SUMPRODUCT(--(Sheet1!F2:F18620=Summary!B3&"*")*--(Sheet1!G2:G18620=101004))

How am I continuing to do this wrong??

• Re: Sums and CountIfs

The only thing I can think of that's causing the 0, could be the *

Are you really looking for the cell value of B3 and immediately after that value an asterisk *?

Wigi

Regards,

Wigi

Excel MVP 2011-2014

For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

-- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

• Re: Sums and CountIfs

Hi,

Try,

=SUMPRODUCT(--(ISNUMBER(SEARCH(Summary!B3,Sheet1!F2:F18620))),--(Sheet1!G2:G18620=101004))

HTH

• Re: Sums and CountIfs

Quote from Wigi

The only thing I can think of that's causing the 0, could be the *

Are you really looking for the cell value of B3 and immediately after that value an asterisk *?

Wigi

What causes my method to not work? In the file I attached it worked.
Kris?

Wigi

Regards,

Wigi

Excel MVP 2011-2014

For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

-- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

• Re: Sums and CountIfs

Nope. Not working for me either.
Now my supervisor's goal is a Macro; in case you couldn't tell, I suck at those.

This is what my supervisor sent me, but it tells me there's a bug:

Sheets("CODTL").Select
Rows("1:1").Select
Selection.AutoFilter
ActiveWindow.ScrollColumn = 1
Selection.AutoFilter Field:=1, Criteria1:="=P2*", Operator:=xlAnd
Selection.AutoFilter Field:=2, Criteria1:="101004"
Selection.AutoFilter Field:=9, Criteria1:="=STATOR K59*", Operator:=xlAnd
ActiveWindow.SmallScroll ToRight:=3
Range("L3066").Select
Selection.AutoFilter Field:=12, Criteria1:=">=2/1/2006", Operator:=xlAnd _
, Criteria2:="<=2/17/2006"
ActiveWindow.SmallScroll ToRight:=33
Range("A1:AR18820").Select
Range("AR1").Activate
Selection.Copy
Sheets("Hartselle").Select
ActiveWindow.ScrollRow = 1
Range("A1").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=207
Range("B228").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("B228").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-226]C:R[-2]C,R[-226]C)"

End Sub

This code tells me that he wants all the items in field 1 that begin with P2. We also need S2. This code creates another page. How would I do this without the bug (it says it's in the row with the # 101004)?

• Re: Sums and CountIfs

Quote from Wigi

What causes my method to not work? In the file I attached it worked.
Kris?

Wigi

Actually my reply was against this

=SUMPRODUCT(--(Sheet1!F2:F18620=Summary!B3&"*")*--(Sheet1!G2:G18620=101004))

SUMPRODUCT won't accept wild cards.

HTH