Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to check 2 columns for text and add a 3rd col to total
Trying to check 2 columns and if I find "Auto" in column A and "January" in
column C then I want to add the money value in column E to a budget total Travel Auto Clothing etc January February Espense item Auto January $50 Travel January $10 Auto February $ 20 Want to get $50 into January Auto, $10 into January Travel and $20 into February Auto. I've tried to use the condition given by Excel 2000 help which states that =SUM(IF((B:B25="Northwind")*(C5:C25="Western"),F5: F25)) and substituted the appropriate columns and text but I only get a #VALUE error or totals for all the data in the money column rather than just the selected values. Also the note says to use CTRL + SHIFT + ENTER before entering the formula because it's an array selection and I've tryed that and it doesn't help. When I edit the formula it highlights the proper array of cells but it doesn't work Can anyone tell me what I may be doing wrong or suggest another formula that might accomplish the same thing? Thanks, Rick |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to check 2 columns for text and add a 3rd col to total
One way:
=SUMPRODUCT(--(B5:B25="Northwind"),--(C5:C25="Western"),F5:F25) Also STRONGLY consider using a pivot table... In article , RickR wrote: Trying to check 2 columns and if I find "Auto" in column A and "January" in column C then I want to add the money value in column E to a budget total Travel Auto Clothing etc January February Espense item Auto January $50 Travel January $10 Auto February $ 20 Want to get $50 into January Auto, $10 into January Travel and $20 into February Auto. I've tried to use the condition given by Excel 2000 help which states that =SUM(IF((B:B25="Northwind")*(C5:C25="Western"),F5: F25)) and substituted the appropriate columns and text but I only get a #VALUE error or totals for all the data in the money column rather than just the selected values. Also the note says to use CTRL + SHIFT + ENTER before entering the formula because it's an array selection and I've tryed that and it doesn't help. When I edit the formula it highlights the proper array of cells but it doesn't work Can anyone tell me what I may be doing wrong or suggest another formula that might accomplish the same thing? Thanks, Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Total using 2 columns for criteria | Excel Discussion (Misc queries) | |||
Total column changes colors when total equals sum of other columns | New Users to Excel | |||
what formula to get total from two different columns | Excel Worksheet Functions | |||
how do i add up 3 columns to get a total | New Users to Excel | |||
How do I subtract two columns from the same total? | Excel Discussion (Misc queries) |