Is this formula style possible?

[renigar]

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 & (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.

 

[SkipVought]

HI,

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Well we don't know what row your table starts/ends. I assume 2/50000.
[tt]
=IF(And(A2<>A3,G2<>G3),SUMPRODUCT(($A$2:$A$50000=A2)*($G$2:$G$50000=G2)*($H$2:$H$50000)),"")
[/tt]
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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

What you need is to
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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

If your problem is the second part of formula (SUM(...)), inside SUM you can use either INDIRECT with dynamic string being proper address, or OFFSET function (in your example to H13 and negative row offset).

combo

 

[renigar]

Skip, the summing of the last row in each group was to show customer annual usage and since each customer could have from 1 to possibly 8 bills in a year (each row of data) I wanted to sum the year on the last row of the year (visually cleaner). I appreciate your input but due to the way my manager wanted the data displayed Combo's suggestion of using INDIRECT worked great, something I was previously unaware of.
Thanks Combo.

 

[SkipVought]

Well this solution should have worked for you if I understood your requirement, without the need for the P & Q columns:
[tt]
=IF(AND(A2<>A3,G2<>G3),SUMPRODUCT(($A$2:$A$50000=A2)*($G$2:$G$50000=G2)*($H$2:$H$50000)),"")
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[renigar]

Skip,
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.