Multiple Coumns Criteria

[Nigel]

I currently use the sumif and countif functions to create some control
values in my VBA code when processing the worksheets. All works fine.

However I now wish to modify the use of each of these functions by testing
the criteria for more than one column.

Currently I have something like.... SumIf(A1:A100,"=A",C1:C100), I now need
to test for two columns

Something like..... SumIf(A1:A100,"=A" and B1:B100,"="B", C1:C100),
obviously this does not work in this form. What do I need to do?

I would rather not create helper columns as I have many combinations of
values to sum across......do I need to write it in code or some sort of
array formula?

Many thanks

 

[Norman Jones]

Hi Nigel,

Try something like:

=SUMPRODUCT((A1:A100="A")*(B1:B100="B"))

and

=SUMPRODUCT((A1:A100="A")*(B1:B100="B")*(C1:C100))

 

[Mike Fogleman]

How about the Sum of two SumIfs?

Sum(SumIf(A1:A100,"=A",C1:C100), SumIf(B1:B100,"=B",C1:C100))

Mike F

 

[Nigel]

Hi Norman
Works great, many thanks