# Sumproduct/Countif

• Hi,

I am working off a seperate worksheet and trying to use a Sumproduct with multiple criterias along with one criteria that calculate all fileds that =<45. The formula I am using is listed below. I get #VALUE!

=SUMPRODUCT(--('Q2-PDR Query BW'!A1:A200="Yes"),--('Q2-PDR Query BW'!B1:B200="Health Net"),--('Q2-PDR Query BW'!C1:C200="Closed"),--('Q2-PDR Query BW'!U1:U200="<=45"))

Any assistance is much appreciated.

Thanks,

Brian

• Sumproduct/Countif

Hi,

I am working off a seperate worksheet and trying to use a Sumproduct with multiple criterias along with one criteria that calculate all fileds that =<45. The formula I am using is listed below. I get #VALUE!

=SUMPRODUCT(--('Q2-PDR Query BW'!A1:A200="Yes"),--('Q2-PDR Query BW'!B1:B200="Health Net"),--('Q2-PDR Query BW'!C1:C200="Closed"),--('Q2-PDR Query BW'!U1:U200="<=45"))

Any assistance is much appreciated.

Thanks,

Brian

• Re: Sumproduct/Countif

Hmmmm.... I wonder...

Is that BW, as in Business Warehouse, as in SAP?

In which case, is it possible the text strings are using the no-break-space character?

...and what's with that last condition? --('Q2-PDR Query BW'!U1:U200="<=45")

Maybe try: --('Q2-PDR Query BW'!U1:U200<=45)

[SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

• Re: Sumproduct/Countif

try

=SUMPRODUCT(--('Q2-PDR Query BW'!A1:A200="Yes"),--('Q2-PDR Query BW'!B1:B200="Health Net"),--('Q2-PDR Query BW'!C1:C200="Closed"),--('Q2-PDR Query BW'![COLOR="DarkRed"]U1:U200<=45)[/COLOR])

• Re: Sumproduct/Countif

I'm still getting "#VALUE!" in the cell.

Thanks

• Re: Sumproduct/Countif

Hi Aaron thanks for responding. I am not affiliated with Business Warehouse.

Anyway I'm still getting "#VALUE!" in the cell.

Thanks

• Re: Sumproduct/Countif

still...

1. Check the text strings you're matching to make sure there are no extraneous space characters in the mix.

2. Make sure it's a space chr(32) and not a NB-space chr(160).

3. Check the values you're testing for <=45 and make sure they're values.

If you can't figure it out from there, post an example.

[SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

• Re: Sumproduct/Countif

Merged the 2 threads - please don't start multiple identical threads - it just wates people's time

Can you attach a very small sample of the workbook?

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!