VLOOKUP and COUNTIF

Maybe in B1:
=SUMPRODUCT(($B$3:$B$270<$A$1)*$A$3:$A$270)
Note that blank cells will be 0 then counted as <A1

I think the answer based on the data in column be should be 150

=COUNT(IF(B3:B260<$A$1,B3:B260))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Here is an alternative to bebo021999's formula, which gives the same result (available in Excel 2007 forward):

Formula:

Please Login or Register  to view this content.

AlKey's result does not seem to be taking the TRUE/FALSE values in column A into consideration.

@6StringJazzer, I maybe wrong, but I think the column A is for illustration purposes.

actually the countifs works better it doesnt count blanks that have true

@AlKey, there are 150 values in column B that are less than the value in A1. If we are after that 150 number, the simplest formula would be

Formula:

Please Login or Register  to view this content.

However, there are 95 TRUE values in column A, and they don't correlate to whether the value in column B is less than A1. The OP seems to looking for the combination of the two, based on:

If the value in column B is less than A1, then I need to count the number of "TRUE" in column A and return that number in cell B1.

opsanalysis07, for this type of problem it always helps to tell us what you think the result should be.

Use the sumifs when you use sumproduct any time you have true
and a blank it gets counted
So if you change one of the cels at the end to
True you get 42 but sumifs gives 41