Formula needed

[Aillas]

I would be grateful to receive a steer as to the formula to use, where I have a list, that involves 20 names, in 5 different areas identified by an abbreviated label, and those names fall into either T or C types which alter frequently although the area and name does not. I need to produce a total for each T or C according to area. without altering the position of the original data.

I have set out a small array showing name, type and class although in practise the name is irrelevant to the totals needed

[TABLE="width: 572"]
<tbody>[TR]
[TD][TABLE="width: 551"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Area[/TD]
[TD]Class[/TD]
[TD] Type St "C"[/TD]
[TD]Type St "T"[/TD]
[TD] Type Ux "C"[/TD]
[TD]Type Ux "T"[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]St[/TD]
[TD]C[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Ux[/TD]
[TD]T[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Donald[/TD]
[TD]St[/TD]
[TD]T[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Richard[/TD]
[TD]Ux[/TD]
[TD]T[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Totals[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]


Easy to do in old fashioned Basic. I have tried match and index but my lack of expertise with Excel a frustrating handicap.

Many thanks for reading this and if you can help that would be appreciated[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Re: Please Help! Formula needed

Hello,

You can produce your expected output in less than 5 seconds ... by inserting a Pivot Table ...

Hope this will help

 

[Aillas]

Re: Please Help! Formula needed

Thanks for this but I don't think this is the answer, (even if I possessed the skills to implement) partly because I need the data to work automatically from the source worksheet (which has other information and fields as well as analysis, and feed the totals automatically into yet another more complex worksheet.

I used just the basic bits that needed comparison and totalling in the example.

 

[James006]

Re: Please Help! Formula needed

Hello,

With your data located in range A1:G5 ... you can test following array formula in cell D2

=IF(MATCH($B2&" "&$C2,SUBSTITUTE(SUBSTITUTE($A$1:$G$1,"Type ",""),"""",""),0)=COLUMN(),1,"")

Hope this will help

 

[DataBlake]

I would be grateful to receive a steer as to the formula to use, where I have a list, that involves 20 names, in 5 different areas identified by an abbreviated label, and those names fall into either T or C types which alter frequently although the area and name does not. I need to produce a total for each T or C according to area. without altering the position of the original data.

I have set out a small array showing name, type and class although in practise the name is irrelevant to the totals needed

[TABLE="width: 572"]
<tbody>[TR]
[TD][TABLE="width: 551"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Area[/TD]
[TD]Class[/TD]
[TD] Type St "C"[/TD]
[TD]Type St "T"[/TD]
[TD] Type Ux "C"[/TD]
[TD]Type Ux "T"[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]St[/TD]
[TD]C[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]Ux[/TD]
[TD]T[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Donald[/TD]
[TD]St[/TD]
[TD]T[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Richard[/TD]
[TD]Ux[/TD]
[TD]T[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Totals[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]


Easy to do in old fashioned Basic. I have tried match and index but my lack of expertise with Excel a frustrating handicap.

Many thanks for reading this and if you can help that would be appreciated[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Without seeing the actual tables, references, and data most of us are going to be scratching our heads as this may not be our line of work/thinking.
If you are proficient with Visual Basic then maybe try using a VBA macro?

from what you said i'm thinking you're looking for =sum(range)
that will give you a total of T and C

so try =sum(D2:G2) in column H

 

[James006]

Hello,

Should you only need the red numbers : Totals in row 6 ... you can test following array formula in cell D6 :

=SUM(IF(MATCH($B2:$B5&" "&$C2:$C5,SUBSTITUTE(SUBSTITUTE($A$1:$G$1,"Type ",""),"""",""),0)=COLUMN(),1,""))

 

[Aillas]

Re: Please Help! Formula needed

Thanks, you are absolutely right, all I needed was the totals in red which in turn I can transfer to another page.

 

[James006]

Re: Please Help! Formula needed

You are welcome