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

ladyck3 · May 13, 2023

#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[tt][/tt]
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.

Deniall · May 13, 2023

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/tec...el-sumproduct-function-in-data-models-138435/

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-excel-sumproduct-function-4178622

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.

mintjulep · May 13, 2023

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.

SkipVought · May 13, 2023

Laurie, very confusing.

Need to see your data.

Please upload a sample workbook.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"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!

SkipVought · May 13, 2023

Here's a guess...

On sheet 2 starting in A1..

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

[tt]
C2: =SUMPRODUCT((Sheet1!A:A=A2)*(Sheet1!G:G))
[/tt]

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,
_{Just traded in my OLD subtlety...

for a NUance!}
"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!

ladyck3 · May 13, 2023

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

SkipVought · May 13, 2023

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

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,
_{Just traded in my OLD subtlety...

for a NUance!}
"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!

ladyck3 · May 13, 2023

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

Deniall · May 13, 2023

Am now home, with access to my accumulated files.

The site I was unable to rustle up yesterday is

http://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.

SkipVought · May 14, 2023

@Deniall,

Thanks for the link for the SUMPRODUCT() function. It, in turn, had a link to

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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,
_{Just traded in my OLD subtlety...

for a NUance!}
"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!

SkipVought · May 14, 2023

Laurie said:
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,
_{Just traded in my OLD subtlety...

for a NUance!}
"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!

ladyck3 · Jun 23, 2023

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.

SkipVought · Jun 24, 2023

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,
_{Just traded in my OLD subtlety...

for a NUance!}
"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!

ladyck3 · Jun 26, 2023

ok, thanks...

SkipVought · Jul 5, 2023

Hey, Laurie how big are your flags?

https://www.fox23.com/news/national...cle_85295a4e-1a58-11ee-83b8-67f3d6bd7239.html

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}
"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!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

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

ladyck3

Technical User

Deniall

Technical User

mintjulep

Technical User

SkipVought

Programmer

SkipVought

Programmer

ladyck3

Technical User

SkipVought

Programmer

ladyck3

Technical User

Deniall

Technical User

SkipVought

Programmer

SkipVought

Programmer

ladyck3

Technical User

SkipVought

Programmer

ladyck3

Technical User

SkipVought

Programmer

Similar threads

Part and Inventory Search

Sponsor