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 Pete,

I noticed the different range lengths in the Formula and amended my copied
version. I still get #NUM.

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

Yes

Pete

Cheers,
Sam

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

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

Hi Teethless mama,

The Formula returns 0 (zero) - not correct.

Cheers,
Sam

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

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

Hi Domenic,

Can you post the formula?

Array Entered:
=IF(ISERR(B5:F5),"",CONCATENATE(COUNTIF($B5:$F5,"< =205"),"/",COUNTIF($B5:$F5,
"205")))

Cheers,
Sam

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

Can you post the formula?

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

Try changing...

ISERR(B5:F5)

to

OR(ISERR(B5:F5))

....and confirm the formula with CONTROL+SHIFT+ENTER. Does this help?

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

Hi Domenic,

Can you post the formula?

Array Entered:
=IF(ISERR(B5:F5),"",CONCATENATE(COUNTIF($B5:$F5,"< =205"),"/",COUNTIF($B5:$F5,
"205")))

Cheers,
Sam

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

Can you post the formula?

Hi Domenic,

I get an incorrect 0 (zero) result.

Cheers,
Sam

Domenic wrote:
Try changing...

ISERR(B5:F5)

to

OR(ISERR(B5:F5))

...and confirm the formula with CONTROL+SHIFT+ENTER. Does this help?

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

It looks like SUMPRODUCT returns 0 because your formula...

=IF(ISERR(B5:F5),"",CONCATENATE(COUNTIF($B5:$F5,"< =205"),"/",COUNTIF($B5:
$F5,
"205")))

....will always return a text string. If I understand correctly, the
intent of the formula is as follows:

1) check B5:F5 for error values, other than #N/A,

2) if an error value exists, leave the cell blank

3) if an error value does not exist, divide the number returned by the
first COUNTIF formula by the number returned by the second COUNTIF
formula.

If this is correct, try...

=IF(OR(ISERR(B5:F5)),"",COUNTIF($B5:$F5,"<=205")/COUNTIF($B5:$F5,
"205"))

....confirmed with CONTROL+SHIFT+ENTER. Does this help?

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

Hi Domenic,

I get an incorrect 0 (zero) result.

Cheers,
Sam

Domenic wrote:
Try changing...

ISERR(B5:F5)

to

OR(ISERR(B5:F5))

...and confirm the formula with CONTROL+SHIFT+ENTER. Does this help?

Hi Domenic,

Thanks for further input. Answered questions below.

Cheers,
Sam

Domenic wrote:
It looks like SUMPRODUCT returns 0 because your formula...

=IF(ISERR(B5:F5),"",CONCATENATE(COUNTIF($B5:$F5," <=205"),"/",COUNTIF($B5:$F5"205")))

...will always return a text string. If I understand correctly, the intent of the formula is as follows:

1) check B5:F5 for error values, other than #N/A,

Yes

2) if an error value exists, leave the cell blank

Yes

3) if an error value does not exist, divide the number returned by the
first COUNTIF formula by the number returned by the second COUNTIF
formula.

No, not divide. I'm using CONCATENATE with the forward slash "/" as a joiner
/ separator between the first COUNTIF result and the second COUNTIF result.

If this is correct, try...

=IF(OR(ISERR(B5:F5)),"",COUNTIF($B5:$F5,"<=205" )/COUNTIF($B5:$F5,"205"))

...confirmed with CONTROL+SHIFT+ENTER. Does this help?

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

I've read through this thread and find myself somewhat confused.

You mentioned the second criteria was giving you the problem. My
understanding is that the second criteria has to do with Column I.

However, when I asked for the formula, it looks like the formula you
posted has to do with Column H.

Can you please clarify?

By the way, in your first post you mentioned that the formula returned
#NUM!. Does your range contain #NUM! values? You can test it by trying
the following...

=COUNTIF(Range,#NUM!)

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

Hi Domenic,

Thanks for further input. Answered questions below.

Cheers,
Sam

Domenic wrote:
It looks like SUMPRODUCT returns 0 because your formula...

=IF(ISERR(B5:F5),"",CONCATENATE(COUNTIF($B5:$F5," <=205"),"/",COUNTIF($B5:$F5"
205")))

...will always return a text string. If I understand correctly, the intent
of the formula is as follows:

1) check B5:F5 for error values, other than #N/A,

Yes

2) if an error value exists, leave the cell blank

Yes

3) if an error value does not exist, divide the number returned by the
first COUNTIF formula by the number returned by the second COUNTIF
formula.

No, not divide. I'm using CONCATENATE with the forward slash "/" as a joiner
/ separator between the first COUNTIF result and the second COUNTIF result.

If this is correct, try...

=IF(OR(ISERR(B5:F5)),"",COUNTIF($B5:$F5,"<=205" )/COUNTIF($B5:$F5,"205"))

...confirmed with CONTROL+SHIFT+ENTER. Does this help?

Hi Domenic,

Apologies to you and Teethless mama. I forgot that I'd conditionally
formatted Column "I" to hide errors.

Answered questions below.

Cheers,
Sam

Domenic wrote:
I've read through this thread and find myself somewhat confused.

You mentioned the second criteria was giving you the problem. My
understanding is that the second criteria has to do with Column I.

Yes

However, when I asked for the formula, it looks like the formula you
posted has to do with Column H.

Can you please clarify?

Yes, apologies.

This is the Formula for Column "I"
=INDEX(Hire,LARGE(IF(Date-DAY(Date)+1=DATE(YEAR($A5),MONTH($B$4),1),ROW(Hire )-
MIN(ROW(Hire))+1),1))

By the way, in your first post you mentioned that the formula returned
#NUM!. Does your range contain #NUM! values? You can test it by trying
the following...

=COUNTIF(Range,#NUM!)

Yes, my sincere apologies. I forgot that I'd conditionally formatted Column
"I" to hide errors.

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

In that case, try the following formula which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=SUM(IF($H$5:$H$42="102/2",IF(ISNUMBER($I$5:$I$42),IF($I$5:$I$420,1))) )

Hope this helps!

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

Hi Domenic,

Apologies to you and Teethless mama. I forgot that I'd conditionally
formatted Column "I" to hide errors.

Answered questions below.

Cheers,
Sam

Domenic wrote:
I've read through this thread and find myself somewhat confused.

You mentioned the second criteria was giving you the problem. My
understanding is that the second criteria has to do with Column I.

Yes

However, when I asked for the formula, it looks like the formula you
posted has to do with Column H.

Can you please clarify?

Yes, apologies.

This is the Formula for Column "I"
=INDEX(Hire,LARGE(IF(Date-DAY(Date)+1=DATE(YEAR($A5),MONTH($B$4),1),ROW(Hire )-
MIN(ROW(Hire))+1),1))

By the way, in your first post you mentioned that the formula returned
#NUM!. Does your range contain #NUM! values? You can test it by trying
the following...

=COUNTIF(Range,#NUM!)

Yes, my sincere apologies. I forgot that I'd conditionally formatted Column
"I" to hide errors.

Hi Domenic,

Thank you very much for your time and assistance. Especially, sorting out my
muddle. Formula works Great! Thanks again.

Cheers,
Sam

Domenic wrote:
In that case, try the following formula which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=SUM(IF($H$5:$H$42="102/2",IF(ISNUMBER($I$5:$I$42),IF($I$5:$I$420,1))) )

Hope this helps!

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

Hi Teethless mama,

Apologies, I forgot that I'd conditionally formatted Column "I" to hide
errors. The #NUM error was hidden away.

Cheers,
Sam

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

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