Formula Help

[VbaHell]

Hello All

I need help please if possible with this formula

SUMIFS(Data!$N:$N/Data!$L:$L,Data!$AT:$AT,"*2015",Data!$Q:$Q,C$15)

Sum Range is N:N which is margin figures
Sum Range L:L is Sales value
Range AT:AT is year
Range Q:Q is account
Cell C15 is account number

What i am trying to complete is the GP% for 2015 by account number

The problem is using N:N/L:L (Sumifs is not liking ths)

 

[Oeldere]

why not make a helpcolumn and add there the formula N/L. After that refer to the helpcolumn in your formula.

 

[Aladin Akyurek]

Why *2105 if AT:AT is year?

 

[VbaHell]

Aladdin l have various years in AT:AT so this is to only calculate lines that match 2015

 

[Aladin Akyurek]

Aladdin l have various years in AT:AT so this is to only calculate lines that match 2015

1) If year values occur in AT:AT in a standalone fashion, there will be no need for a wildcard like *.

2) An operation which affects a reference directly cannot be handled by a range processing function like SUMIFS (that's why Oeldere probably suggests and additional colum.

3) Given the foregoing:

Control+shift+enter, not just enter:

=SUM(IF($AT$2:$AT$400=2015,IF(Data!$Q$2:$Q$400=C$15,IF(Data!$L$2:$L$400,Data!$N$2:$N$400/Data!$L$2:$L$400))))

4) If you want to avoid array-processing:

In X2 enter and copy down:

=IF(L2,N2/L2,"")

Once X is created:

=SUMIFS(Data!$X:$X,Data!$AT:$AT,2015,Data!$Q:$Q,C$15)

Note that for reasons of efficiency, the whole column references should be avoided in array-processing formulas, while it's admissible in range-processing formulas like the latter SUMIFS formula