Summaries

[Lynne]

Hi,
I have data as below and would like to know how to use a INDEX/MATCH to get
the summary captured in the summary below

A BOM 1
A BOM 2
A MAA 1
A BOM 4
A NWD 2
B BOM 1
B NWD 2
B NWD 1
B BOM 2


This should be summaried as follows:
BOM MAA NWD
A 7 1 2
B 3 3

Lynne

 

[mitja.decman]

Hi,
I have data as below and would like to know how to use a INDEX/MATCH to get
the summary captured in the summary below

A BOM 1
A BOM 2
A MAA 1
A BOM 4
A NWD 2
B BOM 1
B NWD 2
B NWD 1
B BOM 2

This should be summaried as follows:
BOM MAA NWD
A 7 1 2
B 3 3

Lynne

I think index and match won't work since you need to sum. Use sumif
function for A row and as range select data from A rows above and then
make another sumif for B row with a range selecting data rows for B.

For example if your first A is in A1 then function to calculate 7 for
A / BOM would be B11 = SUMIF(B1:B5;B10;C1:C5)

Mitja

 

[Guest]

Assuming your data in A2:A10
your summary:
A14: holds A
B13: holds BOM
C13: holds NAA
D13: holds NWD

B14: =SUMPRODUCT(--($A$2:$A$10=$A14),--($B$2:$B$10=B$13),$C$2:$C$10)
Copy across and down

 

[Lynne]

Thanks this is what I required.
Would you please explain the double hypens in the formula and their
relevence.

Lynne

 

[RagDyeR]

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

http://www.xldynamic.com/source/xld.SUMPRODUCT.html


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Thanks this is what I required.
Would you please explain the double hypens in the formula and their
relevence.

Lynne