B
BIG Mike
I am new to working with Excel and have a question regarding a formula I am
trying to build. Thanks in advance for any input !!
I have a workbook set up where there are 12 monthly worksheets (Jan-Dec)
which contain details on commission income. There is a 13th worksheet which
imports data from each of the 12 worksheets. The purpose of the 13th
worksheet is to give an ‘overview at a glance’ of specific pieces of data.
The problem I am having is in determining a correct formula for the overview
sheet cells.
The overview worksheet has columns with heading labels January-December and
rows labeled Company 1, Company 2, Company 3, etc.
I established my first formula which read:
=IF(SUM(Jan!$L$4:$L$100)>0,SUM(Jan!$L$4:$L$100),"")
Column L contains income entries. This formula worked perfectly for what it
was intended, but did not allow me to separate results by company. So, I
added the SUMIF function to my formula.
=IF(SUM(SUMIF(Jan!$C$4:$C$100,$A3,Jan!$L$4:$L$100))>0,SUMIF(Jan!$C$4:$C$100,$A3,Jan!$L$4:$L$100),"")
Column Jan!C contains the abbreviations for Company 1, Company 2, etc.,
indicating which company paid the commission reported in column Jan!L. Cell
A3 (on the overview sheet) contains the abbreviation for the company I want
totals for on this specific line. To my surprise and delight, this also
works perfectly.
The problem I have is that when I enter the company abbreviation on the
monthly worksheets, I will be working quickly and if I inadvertently add a
space before the abbreviation or a space after the abbreviation, it will not
exactly match cell A3 and will not tabulate the numbers correctly.
I looked at the text functions and I see there is the CLEAN function and the
TRIM function. I believe one of these (probably clean) is the function that
will help me, but I have not been able to find a way to insert it into my
existing formula and make it work.
PLEASE HELP !!!
trying to build. Thanks in advance for any input !!
I have a workbook set up where there are 12 monthly worksheets (Jan-Dec)
which contain details on commission income. There is a 13th worksheet which
imports data from each of the 12 worksheets. The purpose of the 13th
worksheet is to give an ‘overview at a glance’ of specific pieces of data.
The problem I am having is in determining a correct formula for the overview
sheet cells.
The overview worksheet has columns with heading labels January-December and
rows labeled Company 1, Company 2, Company 3, etc.
I established my first formula which read:
=IF(SUM(Jan!$L$4:$L$100)>0,SUM(Jan!$L$4:$L$100),"")
Column L contains income entries. This formula worked perfectly for what it
was intended, but did not allow me to separate results by company. So, I
added the SUMIF function to my formula.
=IF(SUM(SUMIF(Jan!$C$4:$C$100,$A3,Jan!$L$4:$L$100))>0,SUMIF(Jan!$C$4:$C$100,$A3,Jan!$L$4:$L$100),"")
Column Jan!C contains the abbreviations for Company 1, Company 2, etc.,
indicating which company paid the commission reported in column Jan!L. Cell
A3 (on the overview sheet) contains the abbreviation for the company I want
totals for on this specific line. To my surprise and delight, this also
works perfectly.
The problem I have is that when I enter the company abbreviation on the
monthly worksheets, I will be working quickly and if I inadvertently add a
space before the abbreviation or a space after the abbreviation, it will not
exactly match cell A3 and will not tabulate the numbers correctly.
I looked at the text functions and I see there is the CLEAN function and the
TRIM function. I believe one of these (probably clean) is the function that
will help me, but I have not been able to find a way to insert it into my
existing formula and make it work.
PLEASE HELP !!!