Totaling different cell content when in a ROW

thunter5 · Jun 2, 2011

Hi, I am using win7/excel 2010

I have a ROW of Data where a cell contains a number/Quantity of an item and next cell has a price of that item in the same ROW.

There are 5000+ ROWS with about 50 COLUMNS of data.

I would like to count the number of Items and then total the prices to get an average price.

The problem I see if I can’t figure out how to tell Excel the difference from a quantity in a cell verses a cell containing a price when it’s in a ROW.

Example is: A1 = 4; B1 = $2; C1 = 13; D1 = $5.12 etc…

I want to be able at the end of this ROW say the TOTAL Items in the row = 17 and the Avg. price per item is $4.38.

I believe the formula is a SUMIF, but I don’t know what to call the cells that have a NUMBER verses those that have Dollar amounts.

Thank you in advance.

Aladin Akyurek · Jun 2, 2011

thunter5 said:
Ok, what this is is a report generated by a program called QUICKBOOKS for my business.

The report is a "Item List" over the years I've been in business. The items are listed by ROW and the Columns are Qt. Sold by Quarter.

I'm trying to get a total of the items sold over the years and the Avg price of those items.

So what would want the formula to do is calculate the total number of items I've sold and what the average price has been.

In the example you asked about, the way it would calculate is:

I would say 4 X $2 = $8 13 X $5.12 = $66.56

Total items = 17
Total Cost = $74.56
Avg. cost Item = $4.39

Thanks again for taking the time to look at this for me Aladin.

TOTAL items...

O2:

=SUMPRODUCT(--(MOD(COLUMN(A1:D1)-COLUMN(A1),2)=0),A1:D1)

AVG cost item...

P2:

=SUMPRODUCT(--(MOD(COLUMN(A1:C1)-COLUMN(A1),2)=0),A1:C1,B1:D1)/O2

Please take notice of A1:C1 vs B1:D1.

Steve=True · Jun 2, 2011

Thanks Aladin, great formulas using sumproduct and mod.

I thought his question was how do i find the $ currency format not how do we look at every other column.

However when i use your formula, the total uses a and c and the average uses b and d.

Perhaps his values didn't change if he changed column a.

Also, how would you modify this for any number of columns in between (or is that what is in your link above?)?

Aladin Akyurek · Jun 2, 2011

Steve=True said:
Thanks Aladin, great formulas using sumproduct and mod.

I thought his question was how do i find the $ currency format not how do we look at every other column.

Yep.

However when i use your formula, the total uses a and c and the average uses b and d.

The formula in O2 uses A1:D1, but in fact it totals the values of A1 and C1.

The formula in P2 needs something different: multiply A1*B1 + C1*D1. That's why the sliding in the range specification. The MOD bits guarantees the right values are multiplied.

Perhaps his values didn't change if he changed column a.

Did not get this...

Also, how would you modify this for any number of columns in between (or is that what is in your link above?)?

In MOD(..,2)=0, 2 means every second or every other. Change that to the desired value.

ROW(...)-ROW(...)+1 or COLUMN(...)-COLUMN(...)+1 effects a sliding within the range of interest.

Hope this helps.

thunter5 · Jun 2, 2011

I will try the new formula later tonight on the actual worksheet.

Thanks Aladin and Steve for your input. This has been a great learning experience.

All the Best!

Totaling different cell content when in a ROW

thunter5

New Member

Aladin Akyurek

MrExcel MVP

Excel Facts

Steve=True

Well-known Member

Aladin Akyurek

MrExcel MVP

thunter5

New Member

Similar threads

Forum statistics

Share this page

Totaling different cell content when in a ROW

thunter5

New Member

Aladin Akyurek

MrExcel MVP

Excel Facts

Steve=True

Well-known Member

Aladin Akyurek

MrExcel MVP

thunter5

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock