Custom Format Text and Calculating Weekly Sales.

%3CLINGO-SUB%20id%3D%22lingo-sub-2721315%22%20slang%3D%22en-US%22%3ECustom%20Format%20Text%20and%20Calculating%20Weekly%20Sales.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2721315%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20everyone.%3C%2FP%3E%3CP%3EI%20need%20some%20help%20with%20the%20redesign%20of%20my%20sales%20report%20sheet.%20I%20work%20in%20an%20optical%20sales%20shop%20and%20in%20calculating%20some%20sales%20of%20products%20we%20offer%20to%20our%20clients%20from%20the%20daily%20sales%20records%20I%20can%20compute%20the%20weekly%20sales%20for%20each%20item%20and%20get%20excel%20to%20give%20me%20the%20month%20overview%20and%20the%20weekly%20sales%20overview.%3C%2FP%3E%3CP%3ENow%20for%20me%20to%20get%20to%20sum%20the%20frame%20sold%2C%20I%20had%20to%20type%20always%20FRAME%3A%20*******%20before%20the%20specific%20name%20of%20the%20frame.%20Now%20I%20felt%20I%20could%20custom%20the%20frame%20cell%20to%20autofill%20FRAME%20via%20custom%20format%20%22FRAME%3A%22%40%20which%20worked%2C%20but%20now%20the%20SUMIFS%20function%20I%20used%20to%20calculate%20the%20revenue%20is%20not%20computing.%20any%20suggestive%20help%26nbsp%3B%20cause%20I%20feel%20it%20is%20the%20last%20part%20of%20the%20formula%20that%20I%20need%20help%20with%3A%26nbsp%3B%3DSUMIFS(%24H%244%3A%24H%24204%2C%24A%244%3A%24A%24204%2C%22%26gt%3B%3D%22%26amp%3BBA209%2C%24A%244%3A%24A%24204%2C%22%26lt%3B%3D%22%26amp%3BBB209%2C%24G%244%3A%24G%24204%2C%24AQ%24212%20%26amp%3B%22*%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2721315%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2721385%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20Format%20Text%20and%20Calculating%20Weekly%20Sales.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2721385%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F528567%22%20target%3D%22_blank%22%3E%40ituryu%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20attach%20a%20sample%20workbook%20(without%20sensitive%20information).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2721406%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20Format%20Text%20and%20Calculating%20Weekly%20Sales.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2721406%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20section%20where%20the%20problem%20is%20located%20is%20in%20Row%20206%20(charts%20and%20Tables%20Analysis)%20and%20also%20in%20Column%20AQ%20to%20AW.%3C%2FP%3E%3CP%3EThanks%20for%20the%20assist.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hi, everyone.

I need some help with the redesign of my sales report sheet. I work in an optical sales shop and in calculating some sales of products we offer to our clients from the daily sales records I can compute the weekly sales for each item and get excel to give me the month overview and the weekly sales overview.

Now for me to get to sum the frame sold, I had to type always FRAME: ******* before the specific name of the frame. Now I felt I could custom the frame cell to autofill FRAME via custom format "FRAME:"@ which worked, but now the SUMIFS function I used to calculate the revenue is not computing. any suggestive help  cause I feel it is the last part of the formula that I need help with: =SUMIFS($H$4:$H$204,$A$4:$A$204,">="&BA209,$A$4:$A$204,"<="&BB209,$G$4:$G$204,$AQ$212 &"*")

4 Replies

@ituryu 

Please attach a sample workbook (without sensitive information).

The section where the problem is located is in Row 206 (charts and Tables Analysis) and also in Column AQ to AW.

Thanks for the assist.

@ituryu 

Use

 

=SUMIFS($H$4:$H$204,$A$4:$A$204,">="&BA209,$A$4:$A$204,"<="&BB209,$G$4:$G$204,"*")

Thank you very much, Hans. Working very well now. Much appreciated.