INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# 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 & (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?

HI,

Skip,

Just traded in my OLD subtlety...
for a NUance!

### RE: Is this formula style possible?

Well we don't know what row your table starts/ends. I assume 2/50000.

=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.

Skip,

Just traded in my OLD subtlety...
for a NUance!

### RE: Is this formula style possible?

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,

Just traded in my OLD subtlety...
for a NUance!

### RE: Is this formula style possible?

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

### RE: Is this formula style possible?

(OP)
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.

### RE: Is this formula style possible?

Well this solution should have worked for you if I understood your requirement, without the need for the P & Q columns:

=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?

(OP)
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.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!