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

#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

Bear the above

caveatin 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

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

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!## RE: Excel An occurance on Sheet #1 - from A:A - want to find the total of $ in G:G ... on Sheet 2

On sheet 2 starting in A1..

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

_{ 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!## RE: Excel An occurance on Sheet #1 - from A:A - want to find the total of $ in G:G ... on Sheet 2

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

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!## RE: Excel An occurance on Sheet #1 - from A:A - want to find the total of $ in G:G ... on Sheet 2

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!!

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

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

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,

_{ 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!## RE: Excel An occurance on Sheet #1 - from A:A - want to find the total of $ in G:G ... on Sheet 2

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!## RE: Excel An occurance on Sheet #1 - from A:A - want to find the total of $ in G:G ... on Sheet 2

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

sample dataand 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!## RE: Excel An occurance on Sheet #1 - from A:A - want to find the total of $ in G:G ... on Sheet 2

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

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

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!