Summarising data.

Would sumproduct be the best formula to use for summarising a list of data? I have a worksheet with 400 rows representing practices.

Each practice currently has a row where they need to fill in columns of data. If they enter data into one column (Col D) I want a summary table showing this column along with the practice contact details in col A, B & C.

Anyone any ideas?

Can you post a sample workbook showing what you need?

Originally Posted by Steve-B

NBVC hows things....

See attached.

Essentially all I want is the summary sheet to show the data in where columns S, W, AA, AE, AI, AM have a 'red figure' (number above 0). Where this is the case I want the data in all the columns on the summary sheet to be filled with the data in each day's record sheet. (This also needs to work as a drop down!!!)

Many thanks as always!

Originally, you said if the user enters something in column D... then show in summary table.. you also didn't specify multiple sheets being summarized....

Also, can't "filter" by font/background colour.. unless you want to get into VBA.. so is the criteria if there is a number in those columns S, W, AA, AE, AI, AM greater than 0, then include? Or if there is a name in Column D include, or what?

See attached for Formula solution:

In each Date sheet, add column AO formula in AO2, copied down to however far you want:

=IF(D2="","",SUM(S2,W2,AA2,AE2,AI2,AM2)>0)

Then in Summary Sheet, Formula in D2 to count number of TRUE in relevant sheet:

=COUNTIF(INDIRECT(C2&"!AO:AO"),TRUE)

Formula in A6:

Please Login or Register  to view this content.

adust ranges to suit your data in date sheets... then Confirm with CTRL+SHIFT+ENTER not just ENTER..

Copy down as far as you want.

Then Copy formula in A6 across all columns...

You will need to adjust the red range to match the columns in the date sheets, then confirm each with CSE keys and copy down.

From PM:

If I wanted to change the drop down list to dates rather than days of the week do I need to update the indirect formula?

So you will have a date, like June 17, 2009 and you want it to know that is a Wednesday and therefore look up the sheet named 'Wednesday', is that correct?

I actually now have to change the tab name and the drop down list to link as a date.

No days will be used a list of dates will be. A workbook will have all the days on the month and a summary sheet.

Hope that makes sense. Thanks for the reply btw!

Format of the date will be dd.mm.yy if you need to know.

Can you re-attach the amended sample workbook so that we are on the same wavelength?

Of course... Many thanks.

Apologies, realised what I needed to change. Think I have it sussed. Basically the format of the tab needs to be the exact same as the drop down list.

Although would this work if I wanted to change the format to say dd.mm.yy?
Many thanks NBVC.

You would need to replace all references to $C$5 with TEXT($C$5,"dd.mm.yy")

Remember to include the formula in AO of each of the sheets so that it works out. =IF(D2="","",SUM(S2,W2,AA2,AE2,AI2,AM2)>0)

NBVC,

When you mention to replace all references to C5
=COUNTIF(INDIRECT(TEXT($C$5,"dd.mm.yy")*C5&"!AO:AO"),TRUE)?

Should be:

=COUNTIF(INDIRECT(TEXT(C5,"dd.mm.yy")&"!AO:AO"),TRUE)

Note: If you add or change a post after resolution, you need to actually add a new reply so that it goes back to the top and alerts the person(s) helping you of a new post... it was just lucky that I came back to this post as I saw you were online.

NBVC,

Last question, when I re-arrnaged the columns and range sizes the formula did'nt pick up the number data for columns L, P T etc. Can you take a look over for me plese?

Many thanks.

For this version of the formula, you need to start your ranges always at row 1...

Also, The condition you set was IF(Condition>0).. but it should be IF(Condition=TRUE) which is what you have in column AI of the specific sheets...

See attached...

You're welcome, Steve.

I noticed, looking back that the condition>0 was I had originally told you.. so that was actually my mistake... sorry....

No problem at all, yet again many thanks.

The only trouble I have is the workbook is now over 112mb and is gonna take a long time to open and formulate.

My only other option was to go down the pivot table route yet I need this to work automatically. It needs to be 100% fool proof........

Is there any way in linking the summary sheet (with the sumproduct formulas) to the data input spreadsheet?

You may be able to reduce the size a little or make the workbook faster if we can change some formulas around... but I need to know if any column in your workbook (especially column B - with codes) is unique in the specific sheets... If they only come up once in the specific sheet you are summarizing, then we can use Vlookup() to get rest of the info.... and that may make workbook process faster....