WorksheetFunction.CountIf and WorksheetFunction.SumIf with 2 conditions?

  • Thread starter Thread starter Etien
  • Start date Start date
E

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
 
Is there a way to get WorksheetFunction.CountIf and .SumIf to work with
2 conditions?
Click to expand...

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...
Click to expand...

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.
 
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
 
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)
 
Is there a way to get WorksheetFunction.CountIf and .SumIf to work with
2 conditions?
Click to expand...
...

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""))")
 
Back
Top