Conditional Sum testing cell format.

[windsurferLA]

I suspect this problem may be solved more simply with a pivot table,
but my users are having problems creating the pivot tables after they
build the data base.

Event data is recorded as pairs in a row where the first cell of the
pair contains the date and the second cell of the pair contains the
data. For example, the sheet where column A, row 9 indicates the hotel
(Hilton), column B will contain the formula, and columns C through L
will contain the data:

Hilton|formula| 1/11/1996 | $56 | 5/8/2004 | $89 |etc… to column “L”

I am looking for a formula that I can insert in column B on the same row
as the data stream which will ignore the dates and give me the sum of
all of the expenditures listed in that row (record).

My approach has been to use the SUMIF function in conjunction with the
“cell” function.

The “cell” function will return a code based on the format of a cell.
The formula shown below will yield a series of true and false answers
when entered into cells in the range D20:M20 (or any other row) based
on whether or not the corresponding cell in row 9 is set to display data
in the General format.

=("G"=CELL("format",D9:M9))

The SUMIF formula returns the WRONG answer when I then used this SUMIF
formula entered as a regular or array formula
{=SUMIF(C13:L13,C13:L13="TRUE",C9:L9)}
The formula returns the sums of the date code numbers.

The SUMIF yields the right answerif I enter as an array formula:
{=SUMIF(C13:L13,C13:L13<>"TRUE",C9:L9)}

The fact that the <> works implies there is some unseen column offset
issue.

When I try to go to the next step and build the cell format test into
eith normal or array configuration of the SUMIF formula,
=SUMIF(C9:L9, ("G"<>CELL("format",C9:L9)) ,C9:L9)
{=SUMIF(C9:L9, ("G"<>CELL("format",C9:L9)) ,C9:L9)}
I get zero as the sum whether I enter it as a regular formula or as an
array formula.

I also have tried using the SUM(IF …. ) in an array formula as.
{=SUM(IF(("G"=CELL("format",C9:L9)),C9:L9))}
{=SUM(IF(AND("G"=CELL("format",C9:L9)),C9:L9))}
{=SUM(IF(("G"<>CELL("format",C9:L9)),C9:L9))}
All yield zero sum.

SUGGESTIONS?

 

[Roger Govier]

Hi

Your data layout is not conducive to a Pivot Table solution.
You would be better having data in just 3 columns with more rows
Location Date Value
Hilton 1/11/96 56
Hilton 5/8/04 89
etc.
Then A Pivot Table would be the most effective solution.

If the data is to remain as shown, then use the following formula in cell B2
assuming that row 1 contains the headers.
=SUMPRODUCT(--(MOD(COLUMN(D2:L2),2)=0),--D2:L2)

Copy down column B as far as required

Regards

Roger Govier

 

[windsurferLA]

Reply to Govier.. at bottom.

Hi

Your data layout is not conducive to a Pivot Table solution.
You would be better having data in just 3 columns with more rows
Location Date Value
Hilton 1/11/96 56
Hilton 5/8/04 89
etc.
Then A Pivot Table would be the most effective solution.

If the data is to remain as shown, then use the following formula in
cell B2 assuming that row 1 contains the headers.
=SUMPRODUCT(--(MOD(COLUMN(D2:L2),2)=0),--D2:L2)

Copy down column B as far as required

Regards

Roger Govier

The data cannot be reformated into three columns as you suggest to
facilitate pivot table because subsequent lines / records, deal with
different hotels.. i.e.

Hilton | |4/8/1996|$25|5/9/1998|$78| ..
Sheraton | |5/6/2001|$99|6/9/2002|$230| ..
DoubleTree| |8/7/2003

The approach you suggested will work if the data is carefully entered as
date then expense without any columns being skipped. I'd like it to be
entered in that way, but it is not always the case.

Following on your approach, might we someone build a vector whose "0"
and "1" values are set by the formating of the individual cells, and
then do a SUMPRODUCT to select the elements that are to be included in
the sum?

WindsurferLA

 

[Roger Govier]

Hi
It doesn't matter at all that they are different hotels. Have a hundred
different hotels if you like. I didn't think that the data would relate to a
single hotel.
The fact is, that data laid out in that format is far easier to manipulate
with Pivot Tables or any other formulae to produce the type of analysis you
want.

It would be possible to convert your data, then carry out the analysis.

Regards

Roger Govier