Data in cells that move

[Figboot]

I have a report that I export to Excel from another program that shows month-to-date transactions in different categories:

****Please see attachment in post below****

I would like to automate a summary of this report to show trends, but the sum for each category is in a different cell depending on how many transactions there have been in the month. There is no constant. The categories (Apples, Oranges, etc) are in A, and the sums are in varrying columns depending on the category.

Is there a way to look for a category in column A, go to column B and sum down to where there is a blank cell? (then /2 to get the actual figure) Or might there be another solution?

Thanks!

[teylyn]

Hi Figboot,

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[Figboot]

Okay...sorry. Attached is an example of what I'm looking to achieve.

[beaunydal]

=SUM(OFFSET(INDIRECT("Data!$B$"&MATCH(Overview!A4,Data!A:A,0)),0,0,IF(ISNA(MATCH(Overview!A5,Data!A:A,0)),65536,MATCH(Overview!A5,Data!A:A,0))-MATCH(Overview!A4,Data!A:A,0),COUNTA(Overview!A:A)))/2
in Overview!B4 filled down to Overview!B6 seems to work OK.
See attachment.
Beaunydal

[Figboot]

Thanks for the help. The issue I am having now (and in an effort to keep things basic failed to mention before) is that there is other data in the sections (both numeric and alpha). Attached is the example. If it helps, while the number of rows for each section will change, but the column with the desired data will remain the same (ex. K for the first section)

I very much appreciate the assistance.

[zulfiqar]

Dear Sir!

I am zulfiqar and need to solve a problem in excel

Sir How can sum the times in hours mintes and seconds

example

A1 = 4 hours 15 mints 34 seconds

A2 = 34 hours 12 mints and 20 seconds

A3 = 2 hours 22 mints and 03 seonds

____________________________________

A4 = sum (?)

Please solve this problem

I shall be thankful of you for this kindness

zulfiqar

[email protected]

[teylyn]

Hi zulfiqar ,

welcome to the forum. However, ....

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

[beaunydal]

Hi Figboot,
Try this attachment.
Same idea as the last except MATCH is used to locate the "Price" heading so that the sum is restricted to just the one column.
I had to change "Due" in 'Planning Report'!G3 to "Overdue" so that the match could be found.

Beau Nydal

[Figboot]

Brilliant! Thank you very, very much Beau. I am forever in your debt.