Now, on the face of it this should be simple, but it has got me stumped
I have a spreadsheet full of data and need to produce summary stats from it.
Only two columns I am interested in
Machine, status
I want to count all machines matching
“Sun*” with a Status of “Live”
“HP*” with a Status of “Unknown”
Simple enough ?
Oh, and it needs to work in excel-2000.
is the * a wildcard? if not, you can do a text join then a countif,
Its a wildcard as the string varies in length and content
Can you afford another column =CONCATENATE(B3,LEFT(A3,3)) where B contains live/unknown and A contains Sun/HP.
Then its just a countif.
I’m sure I’ve seen a macro somewhere that can look at each row, check if a specific detail is there and then copy the data to another sheet. Darned if I can find it at the mo. Will keep looking.
I’ve got a folder full of asstd macros, but you’ll have to wait until tomorrow :wasted: (I wish, football in an hour or so)
DT.
Hmm not trying to be stupid but why not sort column A and B then simply look at count of start/end row?
=SUMPRODUCT(–(A1:A10=“Sun”),–(B1:B10=“Live”))
Or try this out
This is a subset of similar stats by machine, OS, Type, Inside leg measuerment I have to provide on a very regular basis.
Almost certainly more regularly than it is ever looked at.
The state of play now is, when told to jump, asking how high is seen as being confrontational.
Step2000: the above says : 0
Ok that is good but which Column is SUN in and which is Live in?
=SUMPRODUCT(–(A1:A10=“Sun”),–(B1:B10=“Live”)) Adjust as Needed to A1 was the Column is used for SUN and B1 Column was for Type
I’m using
=SUMPRODUCT(–(H2:H1770=“Sun*”),–(M2:M1770=“Live”))
The Sun text column contains a variety of model types after the word Sun but all the ones I am interested in start with Sun
You’ll need to set one up each for the SUN…no wildcard then sum on those answers is all.
Well
=COUNTIF(H2:H1770,“Sun*”)
Gives me the right number so the pattern match works.
Making it a conditional count is where I’m stumped.
I think, top of head stuff, that without using some rather nasty pivots, this will do the job. Due to the formatting in excel, you’ll need to enter into the cell with a ctrl+shift+enter or it goes a bit wobble for some reason.
=COUNT( IF(H2:H1770=“Sun*”), IF(M2:M1770=“Live”) )
DT.
Nope that didnt work either.
Thanks for all the suggestions but the only sensible response is cobblers to Excel, CSV export and do the bloody job in shell/awk/perl.
Just means I will be stuck with it rather than offloading it to somebody else.
The Conditional will not work as it loops. You’ll need to do set several cells with something like this:
=SUMPRODUCT(–(A1:A10=“Sun”),–(B1:B10=“Live”))
=SUMPRODUCT(–(A1:A10=“Sun/HP”),–(B1:B10=“Live”))
=SUMPRODUCT(–(A1:A10=“Sun/IBM”),–(B1:B10=“Live”))
Then just total on the three cells to get answer
Wild card doesn’t work with second conditional statement which sucks!
ok, heres my 2 penneth (and i’ve tried it under 2003…
2 columns… with 2 extra if statements…
Column A = Machine
Column B = Sun Status
Column C = HP Status
Column B =IF(SEARCH(“sun”,A1),“SUN - Live”," “)
Column C =IF(SEARCH(“hp”,A1),“HP - Unknown”,” ")
(I tried the nested if but it didn’t work as expected…
=IF(SEARCH(“sun”,A1),“Sun-Live”,IF(SEARCH(“hp”,A1),“HP-Unknown”," "))
A1 = Sun - result Sun-Live
A1 = hp - result #value!
)
you can then just do a counta as a total to the columns…
Hope that helps.