Hi All,

If possible, I would like to use SUMPRODUCT to achieve the following:

Two ranges - H$5:H$41 and I$5:I$41. Both ranges have some blank and empty
text cells included. Using the flexibility of an input cell to reference the
various Text Labels I would like to count the times a specific Text Label has
a numeric value 0 (greater than 0).

Range H$5:H$41 holds Text Labels using the General Format & Range I$5:I$41
holds Numeric Values using the General Format:

H$5:H$41 I$5:I$42
103/3 20
102/4 0
102/4 10
102/5 0
102/4 0
104/2 10
102/1 10
etc etc


Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200702/1

=SUMPRODUCT(--(H5:H41="102/2"),--(I5:I420))


"Sam via OfficeKB.com" wrote:

Hi All,

If possible, I would like to use SUMPRODUCT to achieve the following:

Two ranges - H$5:H$41 and I$5:I$41. Both ranges have some blank and empty
text cells included. Using the flexibility of an input cell to reference the
various Text Labels I would like to count the times a specific Text Label has
a numeric value 0 (greater than 0).

Range H$5:H$41 holds Text Labels using the General Format & Range I$5:I$41
holds Numeric Values using the General Format:

H$5:H$41 I$5:I$42
103/3 20
102/4 0
102/4 10
102/5 0
102/4 0
104/2 10
102/1 10
etc etc


Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200702/1

Hi Teethless mama,

Thank you for reply and assistance. I get #NUM using the Formula. I'm not
sure if the blank cells and, or cells with "empty text" is causing this?

Further assistance appreciated.

Cheers,
Sam

Teethless mama wrote:
=SUMPRODUCT(--(H5:H41="102/2"),--(I5:I420))

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200702/1

The ranges should be the same length - try this:

=SUMPRODUCT(--(H5:H41="102/2"),--(I5:I410))

or make them both end at row 42 (your posting was ambiguous).

Hope this helps.

Pete

On Feb 9, 3:45 pm, "Sam via OfficeKB.com" <u4102@uwe wrote:
Hi Teethless mama,

Thank you for reply and assistance. I get #NUM using the Formula. I'm not
sure if the blank cells and, or cells with "empty text" is causing this?

Further assistance appreciated.

Cheers,
Sam

Teethless mama wrote:
=SUMPRODUCT(--(H5:H41="102/2"),--(I5:I420))

--
Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-functions/200702/1

Hi Pete,

In the Formula all range lengths are the same. This was a typo "I$5:I$42" in
the Sample Data
heading.

Cheers,
Sam

Pete_UK wrote:
The ranges should be the same length - try this:

=SUMPRODUCT(--(H5:H41="102/2"),--(I5:I410))

or make them both end at row 42 (your posting was ambiguous).

Hope this helps.

Pete

--
Message posted via http://www.officekb.com

Yeah, but the formula from Teethless Mama had both 41 and 42 - did you
copy it directly from his post?

Pete

On Feb 9, 4:19 pm, "Sam via OfficeKB.com" <u4102@uwe wrote:
Hi Pete,

In the Formula all range lengths are the same. This was a typo "I$5:I$42" in
the Sample Data
heading.

Cheers,
Sam

Pete_UK wrote:
The ranges should be the same length - try this:
=SUMPRODUCT(--(H5:H41="102/2"),--(I5:I410))
or make them both end at row 42 (your posting was ambiguous).
Hope this helps.
Pete

--
Message posted viahttp://www.officekb.com

Try to use a smaller range like H5:H10 and I5 to I10 to see if it works, then
increase the range by 5 or 10 until you find the problem.


"Sam via OfficeKB.com" wrote:

Hi All,

If possible, I would like to use SUMPRODUCT to achieve the following:

Two ranges - H$5:H$41 and I$5:I$41. Both ranges have some blank and empty
text cells included. Using the flexibility of an input cell to reference the
various Text Labels I would like to count the times a specific Text Label has
a numeric value 0 (greater than 0).

Range H$5:H$41 holds Text Labels using the General Format & Range I$5:I$41
holds Numeric Values using the General Format:

H$5:H$41 I$5:I$42
103/3 20
102/4 0
102/4 10
102/5 0
102/4 0
104/2 10
102/1 10
etc etc


Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200702/1

Hi Teethless mama,

Thanks for further input. It seems to be where I've got cells returning
"empty text". Can the SUMPRODUCT Formula include criteria to EXCLUDE "empty
text" and "blank cells"?

Cheers,
Sam

Teethless mama wrote:
Try to use a smaller range like H5:H10 and I5 to I10 to see if it works, then
increase the range by 5 or 10 until you find the problem.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200702/1

=SUMPRODUCT(--(H5:H41="102/2"),--(I5:I410),--(ISNUMBER(I5:I41)))

format your result cell as General


"Sam via OfficeKB.com" wrote:

Hi Teethless mama,

Thanks for further input. It seems to be where I've got cells returning
"empty text". Can the SUMPRODUCT Formula include criteria to EXCLUDE "empty
text" and "blank cells"?

Cheers,
Sam

Teethless mama wrote:
Try to use a smaller range like H5:H10 and I5 to I10 to see if it works, then
increase the range by 5 or 10 until you find the problem.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200702/1

Hi Teethless mama,

Thank you very much for reply. I still get #NUM.

I can get the total sum of "102/4" when I use just this:
=SUMPRODUCT(--(H5:H41="102/4"))
gives correct total. But have a problem trying to use the 2nd criteria?

I think the problem is with the "empty text"; the first row in my range has
"empty text". If I exclude the first row I get the correct answer combining
both criteria using your original Formula and the Formula below.
Unfortunately, I do need the first cell in the range as it will not always
contain "empty text". The "empty text" cells use an ISERR error trap to
return the "empty text".

Cheers,
Sam

Teethless mama wrote:
=SUMPRODUCT(--(H5:H41="102/2"),--(I5:I410),--(ISNUMBER(I5:I41)))

format your result cell as General

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200702/1

In article <6d8f62e613384@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

The "empty text" cells use an ISERR error trap to
return the "empty text".

Can you post the formula?

=IF(ISERROR(I5:I41),0,SUMPRODUCT(--(H5:H41="102/2"),--(I5:I410)))


"Sam via OfficeKB.com" wrote:

Hi Teethless mama,

Thank you very much for reply. I still get #NUM.

I can get the total sum of "102/4" when I use just this:
=SUMPRODUCT(--(H5:H41="102/4"))
gives correct total. But have a problem trying to use the 2nd criteria?

I think the problem is with the "empty text"; the first row in my range has
"empty text". If I exclude the first row I get the correct answer combining
both criteria using your original Formula and the Formula below.
Unfortunately, I do need the first cell in the range as it will not always
contain "empty text". The "empty text" cells use an ISERR error trap to
return the "empty text".

Cheers,
Sam

Teethless mama wrote:
=SUMPRODUCT(--(H5:H41="102/2"),--(I5:I410),--(ISNUMBER(I5:I41)))

format your result cell as General

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200702/1