Sumproduct Across A Row

[Guest]

I inserted the following formula into cell C39:

=SUMPRODUCT(--(D3>C3),--(F3>E3),--(H3>G3),--(J3>I3),--(L3>K3),--(N3>M3),--(P3>O3),--(R3>Q3),--(T3>S3),--(V3>U3),--(X3>W3),--(Z3>Y3),--(AB3>AA3),--(AD3>AC3),--(AF3>AE3),--(AH3>AG3),--(AI3>AJ3),--(AL3>AK3),--(AN3>AM3),--(AP3>AO3),--(AR3>AQ3),--(AT3>AS3),--(AV3>AU3),--(AX3>AW3),--(AZ3>AY3),--(BB3>BA3),--(BD3>BC3),--(BF3>BE3),--(BH3>BG3),--(BJ3>BI3),--(BL3>BK3),--(BN3>BM3))

When I enter numbers into cells in the formula cell C39 still displays "0"

Any tips?

 

[Guest]

Hi,

these are all logic so what do you want to do? or what do you want to do if
these conditions were true?

Thanks

 

[Guest]

I want it to display a number of how many are true. Each set is labeled
"Points For" and "Points Against" one of each and want to put it into a cell
under "Losses"

 

[Guest]

I want it to display a number of how many are true. Each set is labeled
"Points For" and "Points Against" one of each and want to put it into a cell
under "Losses"

 

[T. Valko]

The way you have that written, every test must evalaute to TRUE (1)
otherwise you'll get a result of 0. I assume you're using Excel 2007 since
you have more than 30 arguments.

Will every cell in the range have an entry?

C3 = empty
D3 = 10

--(D3>C3) = 1

Try something like this:

=SUMPRODUCT(--(MOD(COLUMN(C3:BM3),2)=1),--(MOD(COLUMN(D3:BN3),2)=0),--(D3:BN3>C3:BM3))

The above formula does not account for empty cells.

 

[Guest]

At the end of the season every cell eill have a number entered. each time I
enter data it will be for a pair when that game is entered (ex. cells P3 and
O3 would be entered at the same time

 

[David Biddulph]

Firstly are you convinced that all 32 conditions are true for the numbers
you have used?
If you are sure, perhaps you could tell us the values in the relevant cells?
I notice that your pattern is reversed for the AI3 and AJ3 condition
compared with the other pairs.

Secondly, which version of Excel are you using? In Excel 2003, it will only
accept 30 arguments to the SUMPRODUCT function, so I had to split the last 2
out into a separate product, but it does what it should do?

If you are still struggling, the advice is the same as for debugging any
long and complex formula, that is to split it into manageable chunks. Copy
the formula & paste it into a number of separate cells, & chop the separate
formulae down to smaller parts to see where you're not getting the expected
result.

 

[T. Valko]

Ok, the formula I suggested should work (as long as you don't insert new
columns to the left of the referenced range).

 

[David Biddulph]

If you want to know how many are true you don't want the product of the
terms, you want the sum, so you can change SUMPRODUCT to SUM.
(And I don't think you'll need the double unary minuses in that case).

 

[Guest]

I'm using Excel 2007.

AI and AJ should be flipped to read: AJ3>AI3


Small example:

Team A Team B Team C
Team D
PF PA PF PA PF
PA PF PA
Team A 7 5
0 5

Team B 4 2 1
3 0 1

Team C 12 8
3 1

Team D 0 4 1 6


Where the vert. is the home team and the team across is the away team. So
I'm trying in a different table on the same worksheet to automatically add up
the wins for the home team (that is why it compares to the cell to it's left)
and then going to flip the sign for the losses in cell D39

 

[David Biddulph]

Yes, that's what I'd guessed.

After my previous post telling you to use SUM rather than SUMPRODUCT (when
you'd said that you wanted to add the terms, rather than take the product),
did you get the result you expected?

Note that you'd probably have found it easier (and not got your AI and AJ
flipped) if you'd listed the cells in order and used
=SUM((C3<D3),(E3<F3),(...
rather than
=SUM((D3>C3),(F3>E3),(...

 

[Guest]

David,

Yes, it worked great. I had used SUMPRODUCT to calculate down the
columns so I just figured I would have to use it across the rows. I have
been using Excel for years only for simple things like entering data and
adding or subtracting a couple cells. Was wondering were I could learn more
about using Excel's formulas since I bought the "dummies" book and doesn't do
much.

Thanks again for taking the time to work this out with me.