INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Jobs

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,

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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.

Entered from my iPad

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close