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

1. ## 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  Register To Reply

2. ## 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  Register To Reply

3. ## 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.  Register To Reply

4. ## 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))  Register To Reply

5. ## 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.  Register To Reply

6. ## 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)  Register To Reply

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

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

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

You're welcome. Thanks for the feedback!   Register To Reply