+ Reply to Thread
Results 1 to 8 of 8

Countifs number of transactions within month and year and match specific location

  1. #1
    Registered User
    Join Date
    08-24-2017
    Location
    DUBAI UAE
    MS-Off Ver
    2021
    Posts
    79

    Countifs number of transactions within month and year and match specific location

    Greetings for the day guys.

    in the attached Excel sheet, from the data range, I need to use a formula in the report summary table, for example, J3 should give me the total number of transaction that was made in the month of Jan year 2022 @ J2 , the data range is in column D, but at the same time it should extract only transaction from location 135 in I3, the data range is B column.

    also, I need to use a formula in the report summary table, for example, K3 should give me the total amount of transaction that was made in the month of Jan year 2022 @ K2 , the data range is in column G, but at the same time it should extract only transaction amount from location 135 in I3, the data range is B column.

    As you can see in the attached screenshot I used the Slicer option, but instead, I want to use the formula as mentioned above.

    I can't attach an Excel file I have no idea why, so I shared a screenshot instead!

    I look forward to finding the right formula for that case.

    many thanks for considering my request.


    example.jpg
    Last edited by MAJID1479; 04-08-2023 at 07:34 AM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,475

    Re: Countifs number of transactions within month and year and match specific location

    Common mistake : applying manual column alignment before making sure everything works. Your sales location in col B are text ( looking like numbers), those on col I are real numbers.
    When no alignment is applied , text aligns left and numbers right.
    Nice layout though ! ( why not use a simple Pivot Table?)

  3. #3
    Registered User
    Join Date
    08-24-2017
    Location
    DUBAI UAE
    MS-Off Ver
    2021
    Posts
    79

    Re: Countifs number of transactions within month and year and match specific location

    @Pepe Le Mokko thank you for your advice, I actually preferred the formula for it.

    I hope there is a suitable formula for it.

  4. #4
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,839

    Re: Countifs number of transactions within month and year and match specific location

    Invalid Attachment

  5. #5
    Registered User
    Join Date
    08-24-2017
    Location
    DUBAI UAE
    MS-Off Ver
    2021
    Posts
    79

    Re: Countifs number of transactions within month and year and match specific location

    Quote Originally Posted by CARACALLA View Post
    Invalid Attachment
    I'm unable to attach files I don't know what the matter is with my account

    Attachment 824908

  6. #6
    Registered User
    Join Date
    08-24-2017
    Location
    DUBAI UAE
    MS-Off Ver
    2021
    Posts
    79

    Re: Countifs number of transactions within month and year and match specific location

    Quote Originally Posted by CARACALLA View Post
    Invalid Attachment
    please if possible refer to the screenshot that I have just added with the updated requirement.

    unfortunately, I'm not able to upload the Excel sheet that's why.

  7. #7
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,475

    Re: Countifs number of transactions within month and year and match specific location

    Like this?
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    08-24-2017
    Location
    DUBAI UAE
    MS-Off Ver
    2021
    Posts
    79

    Re: Countifs number of transactions within month and year and match specific location

    Quote Originally Posted by Pepe Le Mokko View Post
    Like this?
    Thank you brother this is brilliant, I just realized that I need the Furmola to give me an additional extracting result for sale as well.

    so I have just added one more criteria to the formula, J1 is the zone code so the result in J3 is now extracting the same way but also doing an additional match with zone codes in column C .


    However in the K column can I have the same way working for the sale if we assume that sales figures are in column G.

    I look forward to your supportive resolution.

    I'm having trouble with my account otherwise ill upload the updated sheet with sales amounts.

    example.jpg
    Attached Images Attached Images
    Last edited by MAJID1479; 04-08-2023 at 12:14 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Return a specific Year if the month is the last month of the current Year
    By MAJID1479 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-15-2022, 01:42 PM
  2. Replies: 4
    Last Post: 01-07-2022, 11:06 AM
  3. [SOLVED] Formulas for Subcategory Amounts Based on Month and Year from Transactions Sheet
    By BillySpivy in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-16-2021, 05:49 AM
  4. [SOLVED] Countifs by month and Sum does not match Countifs for year, help with syntax needed
    By m_carter in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-08-2021, 10:12 AM
  5. Count number of Dates in a range that match Year and month
    By the_penfool in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-13-2015, 11:49 AM
  6. Replies: 2
    Last Post: 12-17-2013, 06:30 AM
  7. Countifs month and year match
    By jakeisbill in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-11-2013, 03:17 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1