Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
match multiple criteria ina range from multiple criteria multiplet | Excel Worksheet Functions | |||
Can I increase the number of coumns in a worksheet? | Excel Discussion (Misc queries) | |||
How do I change the color of the Rows and Coumns headings | Setting up and Configuration of Excel | |||
delete coumns - shift non-blank cells message | Excel Discussion (Misc queries) | |||
Clustered column chart with stacked coumns | Charts and Charting in Excel |