+ Reply to Thread
Results 1 to 8 of 8

More complex COUNTIF statement than i'm used to proving problematic

  1. #1
    Registered User
    Join Date
    04-01-2014
    Location
    Hong Kong
    MS-Off Ver
    Excel for Mac 2011
    Posts
    4

    More complex COUNTIF statement than i'm used to proving problematic

    I'm trying to, with one formula, get Excel to work out if a column of USD amounts are, individually, >=HKD100,000.00 when converted. I've got a column of USD amounts and a USD/HKD conversion rate on the worksheet. I want Excel to tell me how many of the USD amounts in the column are >=HKD100,000.00 when converted.

    I'm a bit of an Excel novice, so i've been trying to combine COUNTIF criteria without much luck. Getting round the problem, at the moment, by converting the USD amounts into HKD, in the column next to the USD amounts and then employing a simple COUNTIF statement to work out how many of them are over >= HKD100,000.00 in value. But we're dealing with thousands of individual amounts and the aim is to setup a trim and efficient template for the next time we do this.

    So USD amounts in Column A
    The amounts converted into HKD in Column B

    A COUNTIF statement to count how many of the values in Column B are >= 100,000.00

    Thanks in advance.

  2. #2
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: More complex COUNTIF statement than i'm used to proving problematic

    try
    =Countif(B:B,">="&100000)

    if you want to make it dynamic then
    =Countif(B:B,">="&D1) where D1 is the value of 100000 which you can change when required instead of changing formula
    Last edited by hemesh; 04-01-2014 at 05:16 AM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  3. #3
    Registered User
    Join Date
    04-01-2014
    Location
    Hong Kong
    MS-Off Ver
    Excel for Mac 2011
    Posts
    4

    Re: More complex COUNTIF statement than i'm used to proving problematic

    That's half of it, thanks. What I want, with a single statement, is the USD amounts converted into HKD amounts and then a count done to see how many of those converted amounts >= 100,000.

    Something like =COUNTIF(B:B*E$1, ">="&D1), with E1 being the exchange rate. That doesn't work and i'm guessing because the first criteria isn't specific enough.

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: More complex COUNTIF statement than i'm used to proving problematic

    You would need to use a different function, like SUMPRODUCT.

    However, you should avoid using entire columns as range references in the SUMPRODUCT function. Use smaller specific ranges.

    =SUMPRODUCT(--(B2:B100*E$1>=D1))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    04-01-2014
    Location
    Hong Kong
    MS-Off Ver
    Excel for Mac 2011
    Posts
    4

    Re: More complex COUNTIF statement than i'm used to proving problematic

    Genius, thanks. One more thing though, if you would - i'm also looking for a statement to SUM up the USD amounts that are over HKD100,000.00 when converted.

    E.g. in the USD column, I might have:-

    154,777.23
    8,843.91
    7,400.72
    253,922.65
    19,611.50

    I want the statement to look at those USD amounts and the exchange rate and then SUM only the USD amounts that are over HKD100,000.00.

    So similar to your statement, but now I want to SUM the values, as opposed to COUNT how many are over HKD100,000.00 when converted.

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: More complex COUNTIF statement than i'm used to proving problematic

    Not sure I understand. Maybe this...

    =SUMPRODUCT(--(B2:B100*E$1>=D1),B2:B100)

  7. #7
    Registered User
    Join Date
    04-01-2014
    Location
    Hong Kong
    MS-Off Ver
    Excel for Mac 2011
    Posts
    4

    Re: More complex COUNTIF statement than i'm used to proving problematic

    Thanks again, that's done the trick. Got a sleeker worksheet now.

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: More complex COUNTIF statement than i'm used to proving problematic

    You're welcome. Thanks for the feedback!

+ 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. determining multiple cell contents and proving a count
    By m50b25 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-30-2013, 04:24 AM
  2. Summing Times together proving quite tricky
    By controlfreak in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-14-2013, 12:09 PM
  3. Complex countif
    By brad.tho in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 04-04-2008, 07:14 AM
  4. countif complex
    By khalid79m in forum Excel General
    Replies: 1
    Last Post: 12-29-2006, 06:30 AM
  5. [SOLVED] help please - complex countif, etc. functions
    By Jennie in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM

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