| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel lookup in multiple areas
I have a long log and I need to look up and sum if it meets certain criteria.
I have used sumif and that works for one criteria but how do I do a sumif type function if I have more than one. If B1:B100=X and C1:C100=Y then SUM D1:D100 if they contain both X&Y |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel lookup in multiple areas
Use sumproduct
=SUMPRODUCT((B1:B100="x")*(C1:C100="y")*(D1:D100)) -- JNW "Kitbasher" wrote: I have a long log and I need to look up and sum if it meets certain criteria. I have used sumif and that works for one criteria but how do I do a sumif type function if I have more than one. If B1:B100=X and C1:C100=Y then SUM D1:D100 if they contain both X&Y |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel lookup in multiple areas
=SUMPRODUCT(--(B1:B100="X"),--(C1:C100="Y"),--(D1:D100))
Regards Trevor "Kitbasher" wrote in message ... I have a long log and I need to look up and sum if it meets certain criteria. I have used sumif and that works for one criteria but how do I do a sumif type function if I have more than one. If B1:B100=X and C1:C100=Y then SUM D1:D100 if they contain both X&Y |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel lookup in multiple areas
=SUMPRODUCT((B1:B100=X)*(C1:C100=Y)*D1:D100)
HTH Kostis Vezerides Kitbasher wrote: I have a long log and I need to look up and sum if it meets certain criteria. I have used sumif and that works for one criteria but how do I do a sumif type function if I have more than one. If B1:B100=X and C1:C100=Y then SUM D1:D100 if they contain both X&Y |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel lookup in multiple areas
You can use the nested formula CTRL+SHIFT+ENTER, however, I would recommend
using Conditional Sum - Go to Tools/Add ins and select conditional Sum. "Kitbasher" wrote: I have a long log and I need to look up and sum if it meets certain criteria. I have used sumif and that works for one criteria but how do I do a sumif type function if I have more than one. If B1:B100=X and C1:C100=Y then SUM D1:D100 if they contain both X&Y |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I get cell data in multiple Excel files into one summary fi | Excel Worksheet Functions | |||
| Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions | |||
| multi sheet lookup with multiple results | Excel Discussion (Misc queries) | |||
| how can i open multiple excel sheets separately | Excel Discussion (Misc queries) | |||
| Multiple reference for lookup in excel | Excel Worksheet Functions | |||
Linear Mode
