SumIf

[putnum]

I have attached the spreadsheet for those who can help.

If you scroll to the right you will be on the correct place in the sheet.(Starting with I1)

What I need is to be able to come up with a formula for I4 which says if there is not a category or tag than do not include the amounts in the I4 cell.


Is that clear or does it need any more explaining?

Many Thanks!
Adam

[sweep]

Hi,

Try this:

=SUMPRODUCT((L2:L1000)*(M2:M1000<>"")*(N2:N1000<>""))

[putnum]

I am thinking this may need to be a array of some sort but not sure??

[putnum]

Hi,

Try this:

=SUMPRODUCT((L2:L1000)*(M2:M1000<>"")*(N2:N1000<>""))

awesome i think that did it. I am testing it now! I will let you know!!

[rwgrietveld]

Sumproduct deals with that the way SUM() does. My solution is similar to Sweep's

=SUMPRODUCT(--($M$2:M1000<>""),--($N$2:$N$1000<>""),$L$2:$L$1000)

[putnum]

Can ya'll explain what these do in detail by chance?

[shg]

Or, using Excel 2007,

=SUMIFS(L2:L100, M2:M100, "<>", N2:N100, "<>")

[rwgrietveld]

Evaluate the formula and that will bring you all the info you need.

SUMPRODUCT multiplies Arrays (ranges) one by one and then sums these.

e.g.
{1,0,1} * {1,1,0} * {34,64,1} = 34+0+0 = 34

This $M$2:M1000<>"" will give you an array with FALSE, TRUE. Multiplying that with -- will end up in Ones and Twos

[putnum]

sweet well that did it thanks guys!!!