Count data in column x based on conditions in column y

Hi, I am wanting to count the number of records (excluding cells with no value) based on criteria in a corrosponding column.

In column "AS" I have a number of records that are not sorted showing values "7", "13" and "2".

In column "AL" there are values attached to some of these records based on certain IF statement conditions.

I am wanting to count and sum the number of records in column "AL" that meet the conditions of "7" in column "AS" and so forth.

Can anyone help me please?

You can COUNT or you can SUM, up to you:

COUNT:
=SUMPRODUCT(--($AS$1:$AS$1000=7),--($AF$1:$AF$1000<>0))

SUM:
=SUMIF($AS$1:$AS$1000,"=7",$AF$1:$AF$1000)

A sample file would help I think, if you're saying AL contains Numerics and we assume AS also holds 7,13 etc as numbers then I think you're saying you want to use a SUMIF ?

=SUMIF(AS:AS,7,AL:AL)

To count see COUNTIF in XL Help, eg:

=COUNTIF(AS:AS,7)

Though the above obviously would count where AS = 7 regardless of whether AL had no value... without making use of a helper column (concatenating AL & AS into one string) you would need to use SUMPRODUCT preXL2007, eg:

=SUMPRODUCT(--(AL1:AL1000<>""),--(AS1:AS1000=7))

Note use of range 1:1000, pre XL2007 you can not use entire column references in Arrays/Sumproduct (ie AS:AS not viable) moreover you should avoid large ranges when using Arrays/Sumproducts as performance will be affected.

If the above is not what you're after please post a sample file with Dummy data and expected results.

EDIT: the above is essentially a re-iteration of JB's post.