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

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

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

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