PDA

View Full Version : Solved: Sumif using named ranges



Slicemahn
12-19-2007, 07:24 AM
Hello everyone!

I am trying to write code that will sum cells on a condition being met.
First of all I am three named ranges on a sheet called Summary: CableTotal, WirelessTotal and VOIPTotal all refer to a range.


On my present sheet I have a select case statement for cells here is my code:
Sub FormatandReportAdditions()
Dim Endrow As Long
Dim RowLooper As Long
Endrow = Cells(65536,3).End(xlUp).Row
Cells(6, 7).Value = "Total Calls"
Cells(6, 8).Value = "% Calls Tracked"
For RowLooper = Endrow To 7 Step -1
Select Case Cells(RowLooper, 3).Value

Case Cable
Cells(RowLooper,7).Formula= "=sumif(ThisWorkbook.Names("CallInventory").refertoRange.value,Cells(RowLooper,3).Value,ThisWorkbook.Names("CableTotal").RefertoRange.Value)


Now this is where I get stuck. Should I use an array formula instead?

Please let me know your thoughts.

Slicemahn
12-19-2007, 07:26 AM
Oh I almost forgot, here is the attachment with a snip of the information. There are over 32K rows!

rory
12-19-2007, 08:51 AM
Have you tried this:
Cells(RowLooper,7).FormulaR1C1= "=sumif(CallInventory,RC3,CableTotal)"

rory
12-19-2007, 08:54 AM
Just looked at the workbook and there are a couple of things:
1. You need this:
Case "Cable"
since Cable is not a variable (as far as I can tell - no code in the workbook)
2. Your ranges are each defined as two columns for some reason - why?

Slicemahn
12-19-2007, 09:53 AM
There has to be a distinction between French and English that is the only reason. As you can see there are a couple of instances in which French calls are taken in cable , wireless or voip

rory
12-19-2007, 10:08 AM
Oh OK.