Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[Andy H]

Use Frequency?  Done similar, recall basic freq wizard did the job

There was some issue around doing the dates when I played earlier today, then I got sidetracked onto Pivot Tables, and lost 3 hours of my life without achieving anything useful.

Been there. 2 years of hourly readings from the heat meters in a block of 90 flats.

I did manage to get some answers but it wasn't pretty.

I considered saving all the data in a csv file and writing something to suck it all into a mysql or sqlite database but never got around to it.

[Gaffers]

You need to use the COUNTIF function

http://office.microsoft.com/en-001/excel-help/count-how-often-a-value-occurs-HP001127779.aspx

Edit:  you can nest the COUNTIFs with IF statements as required

I Must type faster!

 

I may or may not have spent a whole year in my yoof doing nothing but excel and VBA    (which is the most convoluted way to go about this problem)

[cem_devecioglu]

on complex scenarios using excel built in functions will get more complicated

[Gaffers]

on complex scenarios using excel built in functions will get more complicated

True, but this is not complicated for Excel.  In fact it is exactly what is was designed for?

[jimbobmccoy]

You need to use the COUNTIF function

http://office.microsoft.com/en-001/excel-help/count-how-often-a-value-occurs-HP001127779.aspx

Edit:  you can nest the COUNTIFs with IF statements as required

I Must type faster!

 

I may or may not have spent a whole year in my yoof doing nothing but excel and VBA    (which is the most convoluted way to go about this problem)

I'm currently being tormented by reports for work that are a combo of excel, VBA and tying it together with macro scheduler to achieve some level of automation. Convoluted isn't the half of it!
I try to convince myself it's all good for my 'personal development' but when 64bit excel just decides to stop liking existing VB, it's testing. (Apparently a known issue and why Microsoft recommend using the 32 bit version unless absolutely necessary )

[Gaffers]

Ok, because I am a nice chap, here we go

Number of times name appears - =COUNTIF(C2:C16, "matt")  where C2:C16 is the range of cells and "matt" is the name
Number of times name appears with Action=Yes - =SUMPRODUCT(--(C2:C16="matt"),--(D2:D16="yes"))
Number of time name appears in last 30 days - =SUMPRODUCT(--(C2:C16="matt"),--(E2:E16<(TODAY()-30)))
Number of time name appears in last 30 days with Action=Yes =SUMPRODUCT(--(C2:C16="matt"),--(D2:D16="yes"),--(E2:E16<(TODAY()-30)))

[Gaffers]

Now give me a real challenge

[cem_devecioglu]

on complex scenarios using excel built in functions will get more complicated

True, but this is not complicated for Excel.  In fact it is exactly what is was designed for?

as the murphy rules says it will always get complicated as time goes by

[cem_devecioglu]

Now give me a real challenge

believe me I can

[TheBoy]

Importing into a DB is a no-go I'm afraid. That was the way I wanted to go.

Can you not stick sqlexpress on your own machine, create a one table DB and do it locally on that?

Will anyone mind as long as the output is right?

Also, from experience, management tend to want this type of report to grow-get more complex, analyse the same thing more times, and still not get what it's telling them. As this happens having the info in a proper DB would be a huge help

It has to be self contained and independent of anything else. You're preaching to the converted about databases, but that's not an option.

To really add insult, the data has to be updatable via Excel Webapp, as found on whatever Skydrive is called this week, and ShitPoint.

Grrrr

[TheBoy]

Ok, because I am a nice chap, here we go

Number of times name appears - =COUNTIF(C2:C16, "matt")  where C2:C16 is the range of cells and "matt" is the name
Number of times name appears with Action=Yes - =SUMPRODUCT(--(C2:C16="matt"),--(D2:D16="yes"))
Number of time name appears in last 30 days - =SUMPRODUCT(--(C2:C16="matt"),--(E2:E16<(TODAY()-30)))
Number of time name appears in last 30 days with Action=Yes =SUMPRODUCT(--(C2:C16="matt"),--(D2:D16="yes"),--(E2:E16<(TODAY()-30)))

I'm failing at a hurdle before - I have a list of names, but this list will change over time, as will the number of names in the list.

[TheBoy]

Instead of ="matt", can I have =NameList!A2 instead, would that resolve it? (assume name was stored in NameList!A2) ?

Then with an IF to ensure that NameList!A2 is valid

[Gaffers]

You can reference a single cell easily, but if you wanted to do a number of names in to one result you would need to add OR statements before the first comma by "matt".  Something like:

=SUMPRODUCT(OR(--(C2:C16="matt"), --(C2:C16="Jimbob")),--(D2:D16="yes"))

For a list of results for different names you would need to do a separate formula for each name you waned to count.  SOmething like this:
=SUMPRODUCT(--(C2:C16="matt"),--(D2:D16="yes"))
=SUMPRODUCT(--(C2:C16="Jimbob"),--(D2:D16="yes"))

[TheBoy]

I think that gives me a starter to work from

[Gaffers]

No probs, the hard part is getting the commas and brackets lined up properly

One tip is to fabric some test data to which you already know the outcome and test your formulas on that