## Is this formula style possible?

## Is this formula style possible?

(OP)

I'm wondering is the following example of an excel spreadsheet formula possible and if so what is the correct syntax.

Row 13: If(And(A13<>A14,G13<>G14),Sum(H

I have a sheet with 50000 plus rows. This is a customer list that covers eight years and each customer/year could have from 1 to maybe eight entries. So I am trying to come up with a formula to calculate the starting row of the customer/year (the underlined section) and only show a sum on the last row of the customer/year. I would add two additional columns to the sheet that would (column P) include a running count of the number of entries per customer/year based on customer number and date and (column Q) just be the row number. This calculation gives me the start of the customer year range. The "And" part of the formula would allow the sum to only show on the last row of the customer/year. I have a feeling I am over thinking this and trying to do it the hard way. Can anyone enlighten me.

Row 13: If(And(A13<>A14,G13<>G14),Sum(H

__& (Q13-(P13-1)) &__:H13),""))I have a sheet with 50000 plus rows. This is a customer list that covers eight years and each customer/year could have from 1 to maybe eight entries. So I am trying to come up with a formula to calculate the starting row of the customer/year (the underlined section) and only show a sum on the last row of the customer/year. I would add two additional columns to the sheet that would (column P) include a running count of the number of entries per customer/year based on customer number and date and (column Q) just be the row number. This calculation gives me the start of the customer year range. The "And" part of the formula would allow the sum to only show on the last row of the customer/year. I have a feeling I am over thinking this and trying to do it the hard way. Can anyone enlighten me.

## RE: Is this formula style possible?

Please post a representative portion of your table or upload a test workbook.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Is this formula style possible?

=IF(And(A2<>A3,G2<>G3),SUMPRODUCT(($A$2:$A$50000=A2)*($G$2:$G$50000=G2)*($H$2:$H$50000)),"")

Enter the formula as an array formula (ctr+shift+enter)

However, really need your table. Don't understand why you're only summing the last row of each group.

Entered from my iPad

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Is this formula style possible?

1) copy columns A & G to the right of your table

2) use Data > Data tools > Remove duplicates

3) enter ONLY the SUMPRODUCT formula in the adjacent column, using the first & second columns in that table rather than A2 & G2

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Is this formula style possible?

combo

## RE: Is this formula style possible?

Thanks Combo.

## RE: Is this formula style possible?

=IF(AND(A2<>A3,G2<>G3),SUMPRODUCT(($A$2:$A$50000=A2)*($G$2:$G$50000=G2)*($H$2:$H$50000)),"")

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Is this formula style possible?

I tried your formula and it works great too. Thank you. I didn't try it at first because you were suggesting rearranging the sheet. But it works without any rearranging. Thanks again.