Excel totals

[CV_excel]

i have a spreadsheet with the following columns
ID IM LI SC(IM*LI) IND

I want to display the following summary at the top of the page
1. Total IDs - just the max value of the last row populated. eg if the last row is 12 then total ids should display 12 at the top. I want this auto populated and updated each time a new row is added

2. Column SC- is a multiplication of column IM*LI. I want IND to be auto populated as below
(i) If SC is b/w 1-5 IND=L
(ii) If SC is b/w 6-10=M
(iii) If SC is b/w 11-15=H
(iv) If SC is b/w 16-20=C

3. Total SC summary - at the top I want a table to show total number of Ls, Ms, Hs and Cs

Please can someone help me! I would be very very grateful

Thanks

 

[Special-K99]

You haven't described your spreadsheet that well.
So I must assume things.
If this is wrong adjust the formula to work on your spreadsheet.

Assumptions:
Results will be displayed on row 1 of the spreadsheet
Columns ID IM LI and IND range from column A to E
The columns names are on row 2

in A1
=LOOKUP(2,1/(A3:A1000<>""),ROW(A:A))+2
copy across to C3

in D1
=B1*D1

in E1
=LOOKUP(D1,{1,6,11,16},{"L","M","H","C"})

 

[CV_excel]

Hi, thanks for your reply. Much appreciated. Will try and explain below
Col1=ID
Col2=IM
Col3=LI
Col4=Key(possible values L,M,H,C)
......
Coln

I start populating data from Row 12
(i)In C4, I want to display -Total IDs(i.e max value in Col1) auto populated
(ii)In D5, I want the total number of Ls in Col4

TIA

You haven't described your spreadsheet that well.
So I must assume things.
If this is wrong adjust the formula to work on your spreadsheet.

Assumptions:
Results will be displayed on row 1 of the spreadsheet
Columns ID IM LI and IND range from column A to E
The columns names are on row 2

in A1
=LOOKUP(2,1/(A3:A1000<>""),ROW(A:A))+2
copy across to C3

in D1
=B1*D1

in E1
=LOOKUP(D1,{1,6,11,16},{"L","M","H","C"})

 

[Special-K99]

Mm, in your original description you said
"I want to display the following summary at the top of the page
1. Total IDs..."
The top of the page is row 1

But then you say

"In C4, I want to display -Total IDs"
Row 4 is not the top of the page.

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images (include row and column references so we know where everything is positiioned).

Attachments

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[Peter_SSs]

I start populating data from Row 12
(i)In C4, I want to display -Total IDs(i.e max value in Col1) auto populated
(ii)In D5, I want the total number of Ls in Col4

Try these
C4: =MAX(A12:A1000)
D5: =COUNTIF(D12:D1000,"L")

If you might have data past row 1000, then increase that number in the formulas to something that will always be further down than your data will go.