Our church is selling logo'd clothing to the members.
I have all the orders in a spreadsheet but I can't figure out how to have it calculate pricing. Here is an example...


This particular item is in column C where I have the product code (PR16BLK) at the top (row 1)

I have the member names on the rows going down (3 to 27 for example)


I would like to be able to total the prices of the column depending on the size value in each cell. Example, if a person orders an XL we put that in their cell. I can even get it to display the dollar amount at the bottom using;


What I can't figure out is how to add the others sizes to the equation then total them all up at the bottom?


Am I barking up the right tree? Is this something excel can do?




TIA!

Looks like you need a different layout for your workbook. Perhaps it you could attach a sample workbook with a small amount of data .. .then include a sample sale to be reviewed.

I could tomorrow but it will take me some time to remove the privacy stuff but I'm really close right now.


What I'm doing is inserting a column next to the column I'm trying to calculate with this formula.



The problem is, it's still giving me a 40 when the field is blank (this product not ordered for that person) and I don't know how to say do nothing if the field is blank.



Once I get these columns to work I can hide them and put their total underneath the row I'm calculating.

I am sure there is a much easier way to skin this cat but I think I have liftoff. This gives me the value I need in the column I'll be hiding. I can then make them sum into the visible cell with the total price.

My advice is, design your workbook more database-like. E.g.:
A sheet where all goods are registered in table like
GoodsCode, GoodsName
and define the datarange in column GoodsCode as dynamic named range (all dynamic named ranges mentioned are meant to use as sources for Data Validation lists in other tables);
A sheet where all good types are registered in a table like
GoodsType
with values like "Adult", "Youth", etc., and the datarange is defined as dynamic named range;
A sheet where all goods types and sizes are registered in goods registry table like
GoodsID, GoodsCode, GoodsType, GoodsSize, GoodsPrice
where in columns GoodsCode and GoodsType cells are defined as Data Validation List, to allow select there only goods codes registered in goods table and goods types table respectively, and the column GoodsCodeAndSize is a hidden calculated column, combined from CoodsCode, GoodsType, and GoodsSize values in same row (e.g. "Hood/Adult/XS-XL"), and again defined as Dynamic Named Range;
A sheet, where all members are registered in a table like
Member
, defined a Dynamic Named range;
All above are lookup tables, which you need to update only, when you need to add new members, or goods.

And then the data input table, like
OrderDate, Member, GoodsID, Quantity, UnitPrice, Amount
where UnitPrice is read from goods tegistry table, and Amounts is calculated as Quantity*UnitPrice.

My advice is also to define all those tables as Defined Tables, so all formulas, data validation llist, etc., are updated automatically, whenever a new row is added to table, and the filtering is added by default to all defined tables too. And it will be easy to define any column of defined table as dynamic range too.

Lock headers of all tables, so they will be always visible.

On input sheet, leave at least 2 empty rows at top of page, and into 1st row there enter the subtotal formulas to calculate the sum for filtered values in column where Amount is calculated.

The simples way to get any totals will be to set filters for input table (for OrderDate and Member columns), and the subtotal formula at top of sheet returns the wanted result.

When you want the report in table form, design a report sheet, where you at top of sheet determine e.g. order date, and in table below is displayed a summary for all members gaving ordered any goods in this order. But design of such report will be much more complicated for current post.

Wow, wished I could do that. Most of it I can't even imagine. But now that you mention it, I could have used Access. It's been a while but I think I can still make a database to feed excel.


I pastor a church now. I was much better 10 years ago while I was still in corporate America. But at 61 I've forgotten more than I remember.

So You are then a young guy
Somewhere at your age (more than 10 years ago) I started with SQL Server databases from zero.

A bit shorter(array delimiters might vary depending on regional settings)

BTA a small sample sheet with some desensitized data and expected results would help

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Sure could have used this but with my longer formula I got the task achieved. Thanks for everyone who helped, I really appreciate your assistance.

Do you use it regularly? I find I can still learn if I use it regularly. Otherwise, I have to make step by step instructions so I don't have to reinvent the wheel the next time.

Yes. But mostly to read data from our ERP program, process the read data, and have views in those databases to be read into Excel apps as various reports (mostly graphic ones). The security part of this schema is based on limiting the use of SQL databases data to specific domain user groups. Previously we used SQL Server also as BE for some MS Access apps too, but after the hard- and software and network management was outsourced some years ago, those fell out of use.