sumproduct problems

[sanlen]

Hi,

Could you please advice what is my mistake with the below formula:

=SUMPRODUCT(('Completion FC'!$T$136:$T$199)*('Completion FC'!$D$136:$D$199="BORDER"))

I want to count only those value that the D column equal to "BORDER"

[daddylonglegs]

Do you mean count or sum? Your formula sums column T when column D = "border". Do you get an error? With that syntax any text in the sum range, 'Completion FC'!$T$136:$T$199, will cause a #VALUE! error.

For a single condition SUMIF is preferable, anyway. Try

=SUMIF('Completion FC'!$D$136:$D$199,"BORDER",'Completion FC'!$T$136:$T$199)

[sanlen]

Hi,

I have this data:

border 4-Dec-10
border 5-Dec-10
border 6-Dec-10
city 5-Nov-10
city 2-Jul-10
city 3-Feb-10
city 5-Mar-10
border 5-Aug-10

i want to count how many for the "border" and how many for the "city"

[NBVC]

To count you only need COUNTIF:

=COUNTIF('Completion FC'!$D$136:$D$199,"Border")

and

=COUNTIF('Completion FC'!$D$136:$D$199,"City")

[sanlen]

Hi,

If i have the data as follow:

border 1-Dec-10
city 2-Dec-10
border 3-Dec-10
city 4-Dec-10
border 5-Dec-10
city 6-Dec-10
border 7-Dec-10
city
border

I want to count only those that have completed. Can i do as follow?

=COUNTIF(C3:C11,B3:B11="border")
and
=COUNTIF(C3:C11,B3:B11="city")

It does not work for me.

[NBVC]

Back to Sumproduct, then:

=SUMPRODUCT(ISNUMBER('Completion FC'!$T$136:$T$199)*('Completion FC'!$D$136:$D$199="BORDER"))

assuming if there is a date, then it is considered completed.

[sanlen]

Back to Sumproduct, then:

=SUMPRODUCT(ISNUMBER('Completion FC'!$T$136:$T$199)*('Completion FC'!$D$136:$D$199="BORDER"))

assuming if there is a date, then it is considered completed.

It is working fine with your formula now . However, could you please advice what is the problem with my first formula.

=SUMPRODUCT(('Completion FC'!$T$136:$T$199)*('Completion FC'!$D$136:$D$199="BORDER"))

if it is whatever data and the cell is not blank.

[NBVC]

You don't have a condition of check on the first array.. how you wrote it, it would just try to sum T136:T199 based on the second condition....