If... Sum If

[VegasNath]

I am trying to sum column P if column B = x and column M <> "Accrual"

=IF(Download!$M:$M<>"Accrual",SUMIF(Download!$B:$B,"x",Download!$P:$P),0)

Where am I going wrong?

[HansV]

In Excel 2007 and later, you can use the SUMIFS function:

=SUMIFS(Download!$P:$P,Download!$B:$B,"x",Download!$M:$M,"<>Accrual")

In all versions:

=SUMPRODUCT(Download!$P:$P*(Download!$B:$B="x")*(Download!$M:$M<>"Accrual"))

[VegasNath]

Hans, I get #NUM! (in the SUMPRODUCT version)

[HansV]

Using the first formula or the second one? (It helps me to help you if you're specific)

[VegasNath]

Sorry, see edit.

[HansV]

What happens if you use limited ranges, e.g.

=SUMPRODUCT(Download!$P1:$P1000*(Download!$B1:$B1000="x")*(Download!$M1:$M1000<>"Accrual"))

[VegasNath]

Thanks Hans, got it.

Also, this works, but how can I sum <>"Accrual"

=SUMIF(Download!$M:$M,"Accrual",Download!$P:$P)-SUM(G8:G30)

[HansV]

=SUMIF(Download!$M:$M,"<>Accrual",Download!$P:$P)-SUM(G8:G30)

[VegasNath]

That does not work. I need to sum everything where accrual is not in column M. Something like =SUMIF(NOT(..... but I can't get the syntax correct.

[HansV]

It works in Excel 2007. But try this, in analogy to the earlier formula:

=SUMPRODUCT((Download!$M$1:$M$1000<>"Accrual")*Download!$P$1:$P$1000)-SUM(G8:G30)

[VegasNath]

Sorry Hans, your first attempt was correct, my data was distorted.