Formula Help

VbaHell

Well-known Member
Joined
Jan 30, 2011
Messages
1,208
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)
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Oeldere

Well-known Member
Joined
Dec 29, 2011
Messages
2,213
why not make a helpcolumn and add there the formula N/L. After that refer to the helpcolumn in your formula.
 

VbaHell

Well-known Member
Joined
Jan 30, 2011
Messages
1,208
Aladdin l have various years in AT:AT so this is to only calculate lines that match 2015
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,069
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top