×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

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.

Students Click Here

Excel An occurance on Sheet #1 - from A:A - want to find the total of $ in G:G ... on Sheet 2
6

thread68-1821455

Excel An occurance on Sheet #1 - from A:A - want to find the total of $ in G:G ... on Sheet 2

Excel An occurance on Sheet #1 - from A:A - want to find the total of $ in G:G ... on Sheet 2

(OP)
#1. I do not know VBA or how to use it, so there's that.
#2. I think this might be a pivot table thing, but its been over a decade since I've done those and quite frankly it was rather hit and miss.

SO here we are....
On Sheet1, currently has just over 5,500 records (and growing).
Column A has a list of Categories (20)
Columns B-F are data columns
Column G has a unit price for each record.

On Sheet2, list each of the 20 categories (ColumnA), and the number of occurances per category (ColumnB).

What I want to do is in ColumnC I'd like the total price spent on each category. I just can't figure out how to do this, unsure of how to configure a formula for this purpose.
For instance, categories Apples, Bananas, Cherries

Apples, 324
Bananas, 1,217
Cherries, 3,075

Column C pulling the total of the prices related to each category from Sheet1.
Sheet 2, Columns A, B, C
Category Total Units Total Price
Apples (324) ($125.48)
Bananas (1,217) ($3,053.97)
Cherries (3,075) ($982.36)

I can easily figure out the total units, but pulling out the price from Column G on Sheet1 by adding the prices by line item for each category befuddles me. Now sheet1 is sorted this way and that for different purposes so, it would have to maintain the calculation.

I'm sorry I'm so limited in my knowledge regarding Pivot Tables and/or VBA because I bet that's what I need.
but I really need formulas, those I can figure out when I look at them, I just am not keen on compiling the more difficult ones like I think might be, or not. <shrug>

Thanks,
Laurie
PS, I don't know how to make this test appear in neat columns here, I tried but since I can't figure that out, I put the units and cost in () so you are sure what is what.

RE: Excel An occurance on Sheet #1 - from A:A - want to find the total of $ in G:G ... on Sheet 2

Your description confuses me considerably.  Perhaps a sample spreadsheet would fill in some of the gaps.

Bear the above caveat in mind when I say that I get the feeling you might be able to take advantage of some of the clever uses of the SUMPRODUCT() function.
See
https://www.dummies.com/article/technology/softwar...
in its section headed "A twist on the SUMPRODUCT function" for an rough idea of how this can be achieved.
Or see
https://www.lifewire.com/count-cells-of-data-with-...

There are better sites describing how to drive this "database" capability within SUMPRODUCT, but I am currently away from home and using a friend's computer so I do not have ready access to them.

RE: Excel An occurance on Sheet #1 - from A:A - want to find the total of $ in G:G ... on Sheet 2

Sounds like a pivot table is exactly the thing for this.

If you want to use formulas, then countif() and sumif() will do the job.

RE: Excel An occurance on Sheet #1 - from A:A - want to find the total of $ in G:G ... on Sheet 2

Laurie, very confusing.

Need to see your data.

Please upload a sample workbook.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Excel An occurance on Sheet #1 - from A:A - want to find the total of $ in G:G ... on Sheet 2

Here's a guess...

On sheet 2 starting in A1..


Category Total Units Total Price
Apples	                22
Bananas	                 9
Grapes	                 7
...and the formula in C2...


C2: =SUMPRODUCT((Sheet1!A:A=A2)*(Sheet1!G:G))


I'm assuming several things. I assume, for instance, that you have no headers in your table. I would have a proper table with headers as a Structured Table and the formula would reflect that fact and would, consequently, be more understandable than A1 references yield.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Excel An occurance on Sheet #1 - from A:A - want to find the total of $ in G:G ... on Sheet 2

(OP)
Deniall: Thanks for the suggestions and links, I'll check them out (I wish I could reply to each post individually.

mintjulip: I was afraid of that (Pivot Table) and I'm using countif on some of it and and sumif, didn't give that a thought but I'l need to look it up and refresh my brain :)

SkipVought: I'm so glad to see you are still here... :) Just from looking at the SUMPRODUCT formula, that may work. As for uploading a sample, give me a chance to try SUMPRODUCT and if I still have an issue, I'll try to truncate a sheet to upload. My CHAMPION... OH and I do have column titles/headers on each column... ALWAYS!

I'm sending STARS to each of you because I did pick up details from each of you and I appreciate it!
I'll be back later with confirmation of SUMPRODUCT or an .XLSX upload :)

RE: Excel An occurance on Sheet #1 - from A:A - want to find the total of $ in G:G ... on Sheet 2

Quote (Laurie)

OH and I do have column titles/headers on each column... ALWAYS!

blush I feel ashamed that I underestimated you. So I gave you a star for making a proper table!

But you know that I'm a "knit-picker" 🥴

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Excel An occurance on Sheet #1 - from A:A - want to find the total of $ in G:G ... on Sheet 2

(OP)
SKIP!!!!!!!

WINNER, WINNER, CHICKEN DINNER!!!!!!!!!!!!
DING, DING, DING!!!

=SUMPRODUCT(('!_Library'!A:A=A4)*('!_Library'!G:G))

DID THE TRICK!!!


Unfortunately, I now see the mind-blowing total for-real. <sigh>
At least it is decades worth, or I'd have some 'splainin' to do. LOL

Thank you so very much... I am totally (no pun intended) making note of this! WOOO & HOOO!!
bow
I am not worthy

RE: Excel An occurance on Sheet #1 - from A:A - want to find the total of $ in G:G ... on Sheet 2

2
Am now home, with access to my accumulated files.

The site I was unable to rustle up yesterday is
www.xldynamic.com/source/xld.SUMPRODUCT.html
which is an excellent & all-embracing discussion on the SUMPRODUCT() function.

I also attach (the relevant part of) a spreadsheet I developed for use in describing SUMPRODUCT to uninitiated fellow employees.

RE: Excel An occurance on Sheet #1 - from A:A - want to find the total of $ in G:G ... on Sheet 2

@Deniall,

Thanks for the link for the SUMPRODUCT() function. It, in turn, had a link to http://www.xldynamic.com/source/xld.SUMPRODUCT.htm... that acknowledged Microsoft Excel MVP Ken Wright, who often posted here at Tek-Tips way back in 2003-2008. It was Ken that turned me on to the wonders of the SUMPRODUCT() function and how it worked, having discovered undocumented capabilities of this function.

You link introduced me to the SIGN() function to use when using OR (+) logic on conditional expressions.

Have a little purple star.

I haven't stop learning @81!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Excel An occurance on Sheet #1 - from A:A - want to find the total of $ in G:G ... on Sheet 2

Quote (Laurie)

Unfortunately, I now see the mind-blowing total for-real. <sigh>
At least it is decades worth, or I'd have some 'splainin' to do.

If you have a column of DATES, you could get these totals by YEAR or by QUARTER or by whatever other discriminatory category you might have in your data, that might help you give a clear interpretation of your data.

It all depends on what questions you need to answer or what data might answer anticipated questions.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Excel An occurance on Sheet #1 - from A:A - want to find the total of $ in G:G ... on Sheet 2

(OP)
Hey Skip,
I wasn't aware you had posted again. Now the idea (yes I have a date column in the format of yyyy-mm-dd).
That would be AWESOME! Having it by year or whatever date... So far your solution has been working FANTASTICALLY!!! and I do so appreciate you!

So how do I go about the date part of this ... and this collection even has a record from 1915, seriously.

I have 51 years of collecting and its getting close to $10,000 which stuns me but then again, it is 51 years and it IS my hobby. :)

I'm about to post on another How To, but its a different issue so a new post.

RE: Excel An occurance on Sheet #1 - from A:A - want to find the total of $ in G:G ... on Sheet 2

Laurie, it would be best to start a new thread for this date-related question with all the relevant information, sample data and an uploaded file if that's possible. There are lots of members who could help and I'm willing and able to take a shot again to assist.

Please include some of the questions you would like to have answered that is hidden your data.

Glad it's all working well!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

RE: Excel An occurance on Sheet #1 - from A:A - want to find the total of $ in G:G ... on Sheet 2

(OP)
ok, thanks...

RE: Excel An occurance on Sheet #1 - from A:A - want to find the total of $ in G:G ... on Sheet 2

Hey, Laurie how big are your flags?

https://www.fox23.com/news/national/artist-creates...

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

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! Already a Member? Login
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