WorksheetFunction.CountIf and WorksheetFunction.SumIf with 2 conditions?

[Etien]

Hi there,

Is there a way to get WorksheetFunction.CountIf and .SumIf to work with
2 conditions?

I know how to use SUM((x)*(y)*1) and SUM((x)*(y)*(z)) in Excel
Spreadsheets, but could not find a way to translate this into VBA
code...

Thanks a lot

 

[Harlan Grove]

Is there a way to get WorksheetFunction.CountIf and .SumIf to work with
2 conditions?

No. They're subject to the exact same limitation - single criteria only - to
which COUNTIF and SUMIF are subject in worksheet formulas.

I know how to use SUM((x)*(y)*1) and SUM((x)*(y)*(z)) in Excel
Spreadsheets, but could not find a way to translate this into VBA
code...

Are x and y already arrays of binary values? If not, there's no elegant way to
do this in VBA, so you'd have to iterate in VBA code.

 

[Arvi Laanemets]

Hi

Use SUMPRODUCT function instead, like
=SUMPRODUCT(($A$2:$A$100="xxx")*($C$2:$C$100=1))
to count
and
=SUMPRODUCT(($A$2:$A$100="yyy")*($C$2:$C$100=2)*($F$2:$F$100))
to sum

 

[Bob Phillips]

Etien,

For 2 conditions, and counting, use

=SUMPRODUCT((A1:A100="X")*(B1:B100="Y"))


to sum, use

=SUMPRODUCT((A1:A100="X")*(B1:B100="Y"),(C1:C100))

where C1:C100 has the values to sum

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Harlan Grove]

Is there a way to get WorksheetFunction.CountIf and .SumIf to work with
2 conditions?

...

Since you're qualifying these functions with 'WorksheetFunction.', I've assumed
you're trying to do this in VBA. If you're actually trying to do this in
worksheet cell formula, you've already received two answers with the best
approach - using SUMPRODUCT.

In VBA, this won't work directly because VBA can't produce arrays of booleans
directly as the result of expressions involving other arrays. However, if your
arrays of values are actually worksheet ranges (as they'd have to be in SUMIF
and COUNTIF), you could use Evaluate, e.g.,

result = Evaluate("=SUMPRODUCT((" & x.Address & ">1")*(" & _
y.Address & "=""A""))")