help with sumproduct type problem please :)

[englishdad]

Hi All

I have 2 columns of data, J3:J93 with contract numbers and I3:I93 with amounts. I've got as far as using the following sumproduct function for column O3:O93, which works OK but ends up with duplicates all over the place because I have it on each row. For example, column J may have 90 entries, but only 10 contract numbers are used, therefore my ideal list would contain only 10 contract numbers with 10 totals. I realise that because I'm putting the sumproduct function on each row, I will get 90 results, but I wonder if there is a way to not list duplicates somehow?? In fact the more I think about this the more it seems unlikely, however you peeps are unbelievably good at these sorts of things, so I thought I'd ask...

Thanks very much in advance for any help you can give...


SUMPRODUCT(--($J$3:$J$93=J23),$I$3:$I$93)

 

[Dave Patton]

Did you try a Pivot Table?

Is the list sorted? You could include the total each time the Contract criteria changes.
The summary could be with Excel's Subtotal or formula.

You could prepare a summary table. The summary table can be created with a variety of formulas; I showed three.

Excel 2010
	B	C
1	Summary
2	Contract	Amount
3	2017-ABC	375,000.00
4	2017-ABC	375,000.00
5	2017-ABC	375,000.00
6
6a

Cell Formulas
Range	Formula
C3	=SUMPRODUCT(--($J$3:$J$93=B3),($I$3:$I$93))
C4	=SUMIFS($I$3:$I$93,$J$3:$J$93,B4)
C5	=SUMIF($J$3:$J$93,B5,$I$3:$I$93)

 

[englishdad]

Thanks for your time and help Dave, I completely forgot about a pivot table, which I have now added and it works a treat, but being a little picky about that, if I then change a value in the data, the pivot table stays the same and doesn't update. I would have to delete the table and add a new one. If there was some way of creating a dynamic pivot table to cover the entries as I enter them and update as I enter them, that would be perfection. If not, so be it and it can't be perfect every time!

Again, thanks for your time and help, it is much appreciated

 

[Dave Patton]

Try making your data a Table.
You can name the Table and use that name or the default table name for the Pivot Table data set.
or
Your can create a Dynamic named list for the data.

 

[joeu2004]

I have 2 columns of data, J3:J93 with contract numbers and I3:I93 with amounts. I've got as far as using the following sumproduct function for column O3:O93, which works OK but ends up with duplicates all over the place because I have it on each row.
[....]
SUMPRODUCT(--($J$3:$J$93=J23),$I$3:$I$93)

If you want to calculate the sum for only the first instance of the contract number, enter the following in O3 and copy down through O93:

=IF(COUNTIF($J$3:J3,J3)=1, SUMPRODUCT(--($J$3:$J$93=J3),$I$3:$I$93), "")

Alternatively, if you would like a "table" (range) that contains a unique list of contract numbers (P3:P93) and their sums (O3:O93), enter the following and copy into O4:P93:

O3: =IF(P3="", "", SUMPRODUCT(--($J$3:$J$93=P3), $I$3:$I$93))
P3: { =IFERROR(INDEX($J$3:$J$93, MATCH(0, COUNTIF($P$2:P2, $J$3:$J$93), 0)), "") }

Formulas displayed with curly brackets {...} are array-entered. Type the formulas without the curly brackets, then press ctrl+shift+Enter instead of just Enter. Excel displays the curly brackets in the Formula Bar to indicate that the formula is array-entered.

The formula in P3 assumes that P2 contains something other than a contract number; for example, a column title.

 

[joeu2004]

PS (too late to edit)....

The formula in P3 assumes that P2 contains something other than a contract number; for example, a column title.

Or P2 can be empty.

 

[englishdad]

Thanks very much to you both for the help and code. I've played with all solutions and decided to go with the table option from joeu2004. It's almost (here I go again wanting to be perfect) perfection. Exactly what I need (or should I say want!) but the small niggle is that it lists as it finds, in other words if contract 1227 is listed before contract 1201, it will list 1227 followed by 1201, therefore not in contract/numerical order and I'm not sure if it will sort?

A minor niggle under the circumstances, but still a massive thanks for the help again!

 

[joeu2004]

I'm not sure if it will sort?

Generally, we cannot sort formulas. The cell references usually get all mixed up.

Although some people do construct formulas that result in sorted order, the formulas are complicated and inefficient array-entered monstrosities.

I would prefer to sort the original data first. The table of formulas that I provided should not require any changes because Sort replaces (overwrites) the cells sorted. It does not "move" the cells around, which would convolute the cell references in the formulas.

PS.... The pivot table solution that Dave provided is much more efficient. And it might even sort the result. I don't know: I'm not a PT person.

 

[englishdad]

This is what I was thinking, but sometimes it can't be perfect so I've used your formulas and with an extra 60 seconds worth of effort I just copy the values to a blank area and sort them there, so I'm OK with that Many thanks once again for all your help!
Beers on me!!