Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dual ranges in Excel formulas
I am trying to use dual ranges to add data. I want to sum the notification
devices that are funded. In this example, Column C has three Notification devices, Column B says two of them are funded and I want to add the $650 & $650 together automatically. Cost Funding Status Work $650.0 F Notification devices not loud enough $650.0 F Notification devices not loud enough $600.0 U Notification devices not loud enough $863.0 U Install fire suppression system $600.0 F Install fire suppression system $235.0 U Install fire detection system The formula should look something like If C2:C7="Notification*" and B2:B7="F" then sum A2:A7 thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dual ranges in Excel formulas
use
=sumproduct((b2:b200="f")*(c2:c200="notification etc")*a2:a200) -- Don Guillett SalesAid Software "Bob" wrote in message ... I am trying to use dual ranges to add data. I want to sum the notification devices that are funded. In this example, Column C has three Notification devices, Column B says two of them are funded and I want to add the $650 & $650 together automatically. Cost Funding Status Work $650.0 F Notification devices not loud enough $650.0 F Notification devices not loud enough $600.0 U Notification devices not loud enough $863.0 U Install fire suppression system $600.0 F Install fire suppression system $235.0 U Install fire detection system The formula should look something like If C2:C7="Notification*" and B2:B7="F" then sum A2:A7 thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dual ranges in Excel formulas
hi Bob,
sumprodcut(--(E14:E19="Notification devices not loud enough")*(D14:D19="f"),(C14:C19)) adust the range as you need hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Bob" escreveu: I am trying to use dual ranges to add data. I want to sum the notification devices that are funded. In this example, Column C has three Notification devices, Column B says two of them are funded and I want to add the $650 & $650 together automatically. Cost Funding Status Work $650.0 F Notification devices not loud enough $650.0 F Notification devices not loud enough $600.0 U Notification devices not loud enough $863.0 U Install fire suppression system $600.0 F Install fire suppression system $235.0 U Install fire detection system The formula should look something like If C2:C7="Notification*" and B2:B7="F" then sum A2:A7 thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dual ranges in Excel formulas
=SUMPRODUCT(--(B2:B7="F"),--(LEFT(C2:C7,12)="Notification"),A2:A7)
"Bob" wrote: I am trying to use dual ranges to add data. I want to sum the notification devices that are funded. In this example, Column C has three Notification devices, Column B says two of them are funded and I want to add the $650 & $650 together automatically. Cost Funding Status Work $650.0 F Notification devices not loud enough $650.0 F Notification devices not loud enough $600.0 U Notification devices not loud enough $863.0 U Install fire suppression system $600.0 F Install fire suppression system $235.0 U Install fire detection system The formula should look something like If C2:C7="Notification*" and B2:B7="F" then sum A2:A7 thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel spread sheet wont caculate new data entered in ranges. | Excel Discussion (Misc queries) | |||
trying to import excel database into outlook need to name ranges | Excel Worksheet Functions | |||
Putting Excel formatting and/or formulas into CSV file | Excel Discussion (Misc queries) | |||
How Excel & ACCPAC 6.1 calculate formulas???? | Excel Worksheet Functions | |||
How do I get to master Excel functions and formulas? | Excel Worksheet Functions |