Sum of values over multiple columns

[Dave855]

I have spreadsheet that has the following 3 items PurcNo, Cost and CustNo continually repeated about 30 times.

A B C D E F
ROW 1 PurcNo Cost CustNo PurcNo Cost CustNo
ROW 2 $89 1 $12 1
ROW 3 $89 $12 1

Attached is a the actual file screenshot.

I would like to know a formula that will sum the value in the Cost columns where the value in CustNo column is equal in this instance 1, without having to do dsum... +dsum...+ etc for each column individually like a did and having a extremely long formula.

Please help, i have been searching for days and still without a solution.

[zbor]

Add one row above and write this formula:

A column - leave blank
B column: =SUMIF(C$8:C$2200,1,B$:B$2200)
C Column - leave blank

NBow select all 3 columna A7:C7 and extend to the right

[Ron Coderre]

With
Row_1 containing a series of repeating values: PurcNo, Cost, CustNo

and
AA1: (a CustNo.....eg 1)

This formula sums all Cost values where the CustNo matches AA1

AB1: =SUMPRODUCT((B1:Z1="CustNo")*(B2:Z1000=AA1)*A2:Y1000)

Is that something you can work with?

[zbor]

However, maybe all would be easier with pivot table.

[Dave855]

Hi Guys

Thanks for those of you the replied so quick, is was surprising.

I still am stuck, and have included an xls sheet to make the problem more clear.

Instead of using a heap of sumif functions (potentially 50+) is there a better way to achieve a total result returned into a single sell instead of the function i have used.

Thanks

[Ron Coderre]

Using the workbook you posted...
try this variation of the formula I posted earlier:

C6: =SUMPRODUCT((C11:R11="Cust.Inv #")*(C12:R1000=C4)*B12:Q1000)

Does that help?

[Dave855]

Hi Ron

Thank you for your formula and quick reply.

It is saving me time, apart from the computer taking a 1minute or 2 to recalculate the sheet.