conditional Formula

[Joe]

=SUM(IF($V$3:$V$72=F$5,IF($W$3:$W$72=$C24,$X$3:$X$72,0),0))

I am trying to get a list of data into table format.

if the data in any of the 'V' column agrees to the label at the top and if
the data in any of the 'W' column agrees to the label at the side, then add
up the relevant cells to put it in the table.

I would like to do this with a formula as the data is likely to change on a
monthly basis and I will only need to update the list.

I have put together the above formula but it comes back to '0'.

Can anybody shed any light, it would be very much appreciated.

Thanks

 

[eggman]

one way:

=SUMPRODUCT(--($V$3:$V$72=F$5),--($W$3:$W$72=$C24),--($X$3:$X$72))

If any of the ranges contain numbers and not text, you should be able to get
rid of the "--" on the front of each array.

HTH

 

[eggman]

to clarify my previous post, you can get rid of the "--" for each array that
contains numbers. Text values need to be coerced. So if column X contains
numbers, you do not need to have the "--" infront of that range:

e.g..

=SUMPRODUCT(--($V$3:$V$72=F$5),--($W$3:$W$72=$C24),($X$3:$X$72))

 

[Aladin Akyurek]

=SUMPRODUCT(--($V$3:$V$72=F$5),--($W$3:$W$72=$C24),$X$3:$X$72)

contains 2 conditional expressions, or conditionals for short:

$V$3:$V$72=F$5

$W$3:$W$72=$C24

These would evaluate into arrays of truth values (not text values),
consisting of TRUE's and FALSE's, while the current design of SumProduct
requires them to evaluate into numerical arrays. Hence the double negation
to coerce the truth values into their Excel numerical equivalents: 1's and
0's, repectively. On the other hand,

$X$3:$X$72

is supposed to be numeric (the range to sum), although the comma syntax
allows SumProduct to behave like Sum, that is, ignore any text values in
that range. Prefixing the range to sum also with -- would coerce any
text-formatted numbers into true numbers with the risk of getting #VALUE!
errors regarding true text-values that occur in that range.