Auto Totaling

[rhyan66]

I have a column data on a worksheet, the PO number for specific purchases where a PO number may be listed multiple times. One a separate worksheet within the same workbook, I have a PO summary section where PO numbers are manually entered and the next column sums the the total value of each PO.

Is there a way I could have the Summary section identify POs and automatically total them without having them manually entered?

[JBeaucaire]

To do it in a single cell:

=SUMIF(Sheet1!A1:A1000,Sheet2!B3,Sheet1!C1:C1000)

Where Sheet2!B3 had the PO number you were comparing to the column A on Sheet1, and Sheet1 column C has the values to sum.

[rhyan66]

Im not sure that is working. Ive attached a file that might clarify my goal.

[JBeaucaire]

Yep, totally missed that in the question. Here's a couple of array formulas to do that for you. I can offer a non-array too, but that requires an additional "helper" column in your data. If your sheet isn't big enough to be affected by the arrays shown here, this is good to go.

There is a different formula in A2 from the rest, so don't mix them up. This array system has the added benefit of ordering the list, too. even if it were alphabetic, it would alphabetize them.

[JBeaucaire]

Just for chuckles and giggles, here's the version that uses a helper column to index the first occurrence of each PO number making it simple to draw them into the main list with a simple INDEX/MATCH. Very fast.

Press F9 to watch the whole thing resort, I randomized the entries in the I column to aid the demonstration.

[rhyan66]

Yep, totally missed that in the question. Here's a couple of array formulas to do that for you. I can offer a non-array too, but that requires an additional "helper" column in your data. If your sheet isn't big enough to be affected by the arrays shown here, this is good to go.

There is a different formula in A2 from the rest, so don't mix them up. This array system has the added benefit of ordering the list, too. even if it were alphabetic, it would alphabetize them.

Good afternoon,

I am using the smae formual noted in A2, but when I hit enter, the cell reads "N/A" - any idea what I am doing wrong?

[JBeaucaire]

There are a lot of formulas on my sheet. No idea which formula you've moved, which you're referring to, what you changed (or forgot to) and why it's erroring out.

If I saw the sheet and you pointed all this out, much easier to help.

[martindwilson]

if po only goes up by 1 if newly added and you have as laid out only space for 35 diferent po's then perhaps attached