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.

Steve=True · Jun 2, 2011

This is how you can tell the format of a cell =CELL("format",A1)="c2"

"C2"=currency

Steve=True · Jun 2, 2011

However, i recently found out that the CELL function uses a reference not a range, so you may have to create a helper column with this value.

thunter5 · Jun 2, 2011

Hi Steve

Thank you for the reply.

This didn't see to work or I didn't deploy it correctly. I think you're trying to give the cell a reference or value that can be identified differently then that of the other cells in the row. (Number vs. Currency). This didn't seem to work for me here.

Thanks again for the suggestion

Aladin Akyurek · Jun 2, 2011

thunter5 said:
..

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

TOTAL:

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

AVG:

Control+shift+enter, not just enter...

=AVERAGE(IF(MOD(COLUMN(A1:D1)-COLUMN(A1)+1,2)=0,A1:D1))

thunter5 · Jun 2, 2011

WOW Aladin This WORKED!

I don't know how it worked but I'm looking forward to reading more of my Mr. Excel "Excel 2010 In Depth" book and and figuring this out.

I've been working on this for days and should have posted it here long ago.

Thanks again.

Tom

Aladin Akyurek · Jun 2, 2011

thunter5 said:
WOW Aladin This WORKED!

Great. Thanks for providing feedback.

I don't know how it worked but I'm looking forward to reading more of my Mr. Excel "Excel 2010 In Depth" book and and figuring this out.

...

No idea whether Bill includes this formula which I devised eons ago:

http://www.mrexcel.com/forum/showthread.php?t=22668&highlight=dedicate+Juan (post #7)

thunter5 · Jun 2, 2011

You're welcome Aladin

One other thing, after I wrote you back, I did another test and changed the quantity in one of the cells to see if the average price changed. It didn't, so I'm wondering if I just entered the formula wrong?

Aladin Akyurek · Jun 2, 2011

thunter5 said:
You're welcome Aladin

One other thing, after I wrote you back, I did another test and changed the quantity in one of the cells to see if the average price changed. It didn't, so I'm wondering if I just entered the formula wrong?

The formula for AVG I posted does not look at the quantites. If should, would you decribe the procedure for calculating the average for:

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=256><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>$2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>$5.12</TD></TR></TBODY></TABLE>

which is in A1:D1?

thunter5 · Jun 2, 2011

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.

Totaling different cell content when in a ROW

thunter5

New Member

Excel Facts

Steve=True

Well-known Member

Steve=True

Well-known Member

thunter5

New Member

Aladin Akyurek

MrExcel MVP

thunter5

New Member

Aladin Akyurek

MrExcel MVP

thunter5

New Member

Aladin Akyurek

MrExcel MVP

thunter5

New Member

Similar threads

Forum statistics

Share this page

Totaling different cell content when in a ROW

New Member

Excel Facts

Well-known Member

Well-known Member

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

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