Multiple Coumns Criteria
 

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


--
Cheers
Nigel

Multiple Coumns Criteria
 

Hi Nigel,

Try something like:

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

and

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

---
Regards,
Norman


"Nigel" wrote in message
...
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


--
Cheers
Nigel

Multiple Coumns Criteria
 

How about the Sum of two SumIfs?

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

Mike F
"Nigel" wrote in message
...
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


--
Cheers
Nigel

Multiple Coumns Criteria
 

Hi Norman
Works great, many thanks

--
Cheers
Nigel



"Norman Jones" wrote in message
...
Hi Nigel,

Try something like:

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

and

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

---
Regards,
Norman


"Nigel" wrote in message
...
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


--
Cheers
Nigel