SUMIFs for particular cells in blocks of data

R

rockfalls3

I have a database export file that includes multiple (1100+) blocks of
data in a standard format.

Per block, the format is:
-- The first column is a merged cell of 9 rows, with the unique
identifier for the data block.
-- The next five columns form a sub-block of data related to that
item in the first column.
-- This sub-block has a format of:
-- Row 1: Titles of the five columns in the sub-block.
-- Rows 2-8: data (column 1 contains the Titles/names for each
row).
-- Row 9: Totals for each column's worth of data.

I want to go through the whole export file (spreadsheet), summing
particular cells in Row 9,
based on the unique identifer in column 1.

Can I use VLOOKUP and SUMIF for this? (If so, how? If not, then how do
I find the blocks I
want to use for my calculation and then choose the correct cell in Row
9 for that block?)

I had thought of taking each row and separately copying it to form a
single row per unique
identifier, and then using VLOOKUP and SUMIF without the added fun of
navigating the sub-
block, but my preference is to not have this intermediate reformatting
step.

A related question: can I use PERCENTILE if I have such non-contiguous
blocks of data?

Any help/insights/commands/etc. would be greatly appreciated.

advTHANKSance....
 
S

Spiky

I would turn your unique identifiers into Named Ranges specifying your
blocks of data. Then it should be easy to do what you want, although
you may need to use INDIRECT to create the references.
 
S

Sheeloo

It would help to understand better if you can paste two blocks of data.

You may send the complete file if it does not contain confidential data.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top