This would be easy if I had a small range of values to count by using the SUM and IF functions, but the data looks like this below, and runs to over 1000 lines.

Functional Loc.
CNC-01-ACO-078 -LI
CNC-01-ADG-PCV -003
CNC-01-ADG-PCV -003
CNC-01-ADG-PCV -045
CNC-01-ADG-UV -055
CNC-01-ADG-UV -055
CNC-01-APB-UV -003
CNC-01-APB-UV -003
CNC-01-APH-065 -TE
CNC-01-APH-UV -003
CNC-01-APH-UV -007
CNC-01-CET-LCV -004
CNC-01-CEX-089 -FT
etc.

So, is there a way of counting how may times a ‘functional location’ appears in that list, without having to state each one, then output that data into a table? Basically I want the SUM bit without having to specify an IF bit.

Thanks.

pivot table?

Pivot table would be first choice, faiiling that countif function

Thanks gents, that’s a big help. I’ve used the pivot table to generate a list of the unique values in my data, which makes it easy to do the countif (not sum & if 🙄 ) function to find how many times.

It’s a bit manual, but doesn’t take long. Cheers.

Eh?

Just put Functional location into the “Row Labels” and “Values” areas of the pivot table.

coincidentally I posted this a while back, if you have the time it’s worth having a go at learning it as it’s a technique that will do what you’re after (and more).

http://singletrackworld.com/forum/topic/just-learnt-a-powerful-new-excel-technique

Get your SAP team to write a quick report 🙂

Long-winded method tthew & not using pivot table to full potential. Njee has it:

Highlight “Functional Loc” column, Insert pivot table. Tick the box at the top of the pivot table box, which should place “Functional loc” in the “Row lables” box. Drag “Functional Loc” into the “Values” box. It should contain “Count of Functional Loc” & the number of instances for each reference is produced.

Stoner – Member
coincidentally I posted this a while back, if you have the time it’s worth having a go at learning it as it’s a technique that will do what you’re after (and more).

http://singletrackworld.com/forum/topic/just-learnt-a-powerful-new-excel-technique

Ummm… it *may* not matter too much but I’m back to a bit of Excel dev. again these days and now know what it was I use to get round Offset() not being able to use Indirect()

Option Explicit
Public Function DyIndirect(sName As String) As Range
””””””””””””””””””””””””””””””””””’
‘Allows a cell to be referenced that contains a range name in the ‘
‘way you’d expect INDIRECT() funtion to work, but doesn’t. ‘
”””””””””””””””””””””””””””””””””””””””””
‘Modified by Rob Hilton 20/07/09 from code pinched from here: ‘
‘http://www.dailydoseofexcel.com/archives/2005/03/01/indirect-and-dynamic-ranges/’
”””””””””””””””””””””””””””””””””””””””””
Dim nName As Name
Application.Volatile
‘Make sure the name supplied exists
On Error Resume Next
Set nName = ActiveWorkbook.Names(sName)
Set nName = ActiveSheet.Names(sName)
On Error GoTo 0

‘Set the function to the range or return the name error
If Not nName Is Nothing Then
Set DyIndirect = nName.RefersToRange
Else
DyIndirect = CVErr(xlErrName)
End If
End Function

cheers Rob. I was just reading through to the end of that thread and realised I’d not resolved that bit. Next time I play with similar I shall have a go with your piece of VBA, (which may be sooner than it might, I have a project coming up which I think may call on something like this)

Cheers

Get your SAP team to write a quick report

Ah ha ha ha ha ha ha hah!!……
Would be quicker for me to write it all out by hand and count up on my fingers.

Just put Functional location into the “Row Labels” and “Values” areas of the pivot table

It won’t let me put the same field in two ‘areas’. My way is functional enough, I’m happy with that.

🙂

Where are you getting the data from?

It’s from SAP as suggested up there, exported into a spreadsheet. I think I can automatically populate a Pivot Table, will have a go at that later.

It should let you put it in values and another area, although not in two of row/column/filter.

Where in SAP though?

FWIW, you can export straight into a pivot table from most ALV (the grid type) reports.

Where in SAP though?

List of maintenance notifications. Transaction IW29. It’s as part of a monthly report I do looking at operational losses at our power station, and I have been asked to include assets that are repeatedly breaking down, (even where there is no associated generation loss)

Me, yesterday.

🙂

You could set a display variant to just list out the functional locations and then a direct export to pivot table

Or do you have access to SQVI or SE16(N) (though actually I don’t think that’ll be any faster)