# Sumproduct or Match(?) problem

#### konew1

##### Well-known Member
I have about 10 sets of data each headed 1, 2, or 3 (only 2 sets in example) and lots of lines each with a code in column B. The codes repeat several times

I need a summary panel so that each of the column B codes shows a single result for the total of all the rows with a particular code (eg BANK) with separte results for the columns headed 1,2 and 3.
I have tried sumproduct and sumif with no luck. I think it needs a CSE but need help there.
Book31.xls
ABCDEFGHI
23123123
24IssuedCapitalCAP(1,000)(1,000)(1,000)(23,331)(23,331)(23,331)
25RetainedEarningsB/FwdRE--(8,788)-(30,227)(37,303)
26Hedgingreserve(E)HDG
27DividendPaidDIVD---
28Bank-MaintenanceFund(HHV)BANK-37,52841,951
29Bank-TillClearinga/cBANK3,336660598
30Bank-APClearinga/cBANK---
31EFTPOSClearinga/cBANK---
32NationalBank-00a/cBANK60,14252,88635,917598,43111,97516,716
33NationalBank-02a/cBANK1,2083,5731,742
34Bank-MaintenanceFund(LPV)BANK
35
36SUMMARY
37123
38BANK663,117106,62296,925
39CAP-24,331-24,331-24,331
40HDG
Sheet1

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### dafan

##### Well-known Member
This goes in C38, and you can figure out the rest hopefully
Code:
``=SUMIF(\$B\$28:\$B\$34,B\$38,C\$24:C\$34)``
(Drag down and right)

##### MrExcel MVP
1) if possible, reorganise your data into a form better suited to excel & just use the standard functions

2) if not, formulas of the form:

=SUM(IF(C23:C31="BANK",IF(MOD(COLUMN(D22:N31),4)=0,D23:N31)))
...entered with control + shift + enter, not just enter. alter the =0 check on the mod() for other columns.

#### konew1

##### Well-known Member
I think you had a typo, so I changed it to =SUMIF(\$B\$24:\$B\$34,B\$38,C\$24:C\$34)
That only picks up column C data. I also need column G, and K and every 4th column after that up to column AW. The formula in D38 needs to add column D, H , L etc

#### dafan

##### Well-known Member
Wow then I have to agree with PaddyD, thats a weird lay-out.

But this will work too:
Code:
``=SUMPRODUCT((B24:B34=B38)*(C23:AW23=1)*(C24:AW38))``

Replies
1
Views
820
Replies
1
Views
546
Replies
7
Views
896
Replies
1
Views
142
Replies
3
Views
245

1,191,199
Messages
5,985,234
Members
439,952
Latest member
djharter

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back