+ Reply to Thread
Results 1 to 2 of 2

sum and frequency of data & sum of amount based on criteria

  1. #1
    Registered User
    Join Date
    01-07-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    10

    sum and frequency of data & sum of amount based on criteria

    Hi guys,

    ecountered some errors in my excel sheet.
    Sample excel.xlsx


    As you can see from the sample excel provided, i'm trying to derieve the number of unique clients from a specific country, however therea are some blanks, would this affect the function used?
    The function (kindly provided by benishiryo) worked well on my original data sheet and in this example, HOWEVER for the cell with country "Singapore" with over 3000 accounts, i got an "#N/A" error. Not sure whats wrong with it :/

    i'm trying to derieve also, the total amount of money from the accounts under this specific country, however i have some that are labeled as "NA" and some that are blanks, would these affect the function used?

    IF anyone can be so kind to help me out.

    Thanks in advance
    Last edited by joeldlow; 01-09-2014 at 02:26 AM. Reason: re-attachement

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,607

    Re: sum and frequency of data & sum of amount based on criteria

    As for C3 problem - this is array formula. You shall press Ctrl+Shift+Enter to make it working. So go into editing (press F2 while in the C3 cell) and press and hold Ctrl+Shift and then press Enter and release all 3 keys.

    As for column E - you do not have '1) Main table' sheet in your file there is only 'main table'
    besides this formula would always return 0 (if error is corrected) because it makes counts cases when all conditions are valid silmultanously. What is more (but it really does not matter in view of 2 earlier comments) - formulas in E2 and E3 refer to Bahrain - you do not have such data.

    edit: Just to make sure we understand each other well, I add corrected attachment
    Attached Files Attached Files
    Last edited by Kaper; 01-09-2014 at 03:17 AM.

+ 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. Replies: 14
    Last Post: 08-21-2012, 02:17 PM
  2. Replies: 14
    Last Post: 05-23-2012, 08:09 PM
  3. [SOLVED] SUMPRODUCT to calculate amount based on different criteria
    By pjlau in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-30-2012, 05:41 AM
  4. How can I calculate amount of time left based on amount spent?
    By KLD in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-23-2006, 11:25 AM
  5. sumif-add amount to another cell based on two criteria
    By gabrielinlompoc in forum Excel General
    Replies: 0
    Last Post: 02-09-2006, 08:25 PM

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