SUMPRODUCT - 2 Criteria

[Sam via OfficeKB.com]

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

 

[Guest]

=SUMPRODUCT(--(H5:H41="102/2"),--(I5:I42>0))

 

[Sam via OfficeKB.com]

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

 

[Pete_UK]

The ranges should be the same length - try this:

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

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

Hope this helps.

Pete

 

[Sam via OfficeKB.com]

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

The ranges should be the same length - try this:

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

 

[Pete_UK]

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

Pete

 

[Guest]

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]

Hi Pete,

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

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

Pete

Cheers,
Sam

 

[Sam via 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

 

[Guest]

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

format your result cell as General

 

[Sam via OfficeKB.com]

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

 

[Domenic]

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

Can you post the formula?

 

[Guest]

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

 

[Sam via OfficeKB.com]

Hi Teethless mama,

The Formula returns 0 (zero) - not correct.

Cheers,
Sam

 

[Sam via OfficeKB.com]

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]

Try changing...

ISERR(B5:F5)

to

OR(ISERR(B5:F5))

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

Can you post the formula?

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

Cheers,
Sam

Can you post the formula?

[/QUOTE]

 

[Sam via OfficeKB.com]

Hi Domenic,

I get an incorrect 0 (zero) result.

Cheers,
Sam

 

[Domenic]

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?

 

[Sam via OfficeKB.com]

Hi Domenic,

Thanks for further input. Answered questions below.

Cheers,
Sam

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

...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.

 

[Domenic]

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!)