Sum If formula with Conditional Formatting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a worksheet with a cell in which I would like to make the following work

{=SUM(IF(('ATS FY04 LANT Device Costs'!C2:C43="2F166-2")*('ATS FY04 LANT Device Costs'!F2:F43=K16),'ATS FY04 LANT Device Costs'!G2:G43))

The range C2:C43 contains simulator device names (device name appears more than once in range), range F2:F43 contains numbers (4-16) which are the operating hours per day a device can be contracted, and range G2:G43 are the dollar costs.

If I just enter a value (ie number 6) in cell K16, the formula works correctly and returns the value ($) for the selected device and operating hour choice. But what I want to make happen is for K16 to be the linked cell of a combo box allowing the user to select the operating hours he wants from the available range for a specific device. I have the combo box set up correctly, and if the user selects a value from the combo box the value is assigned to cell K16 (if I go to cell M19 and enter =K16 it will show the value the user selected from the combo box) but the above formula will always return 0

Any suggestions/ideas? (and Yes, the brackets are placed by using Ctrl-Shift-Enter after entering the formula)
 
Hi
why not use 'Data - Validation - List' for this. Also make sure that
your listbox valuea are numbers and not 'text' values. See:
http://www.contextures.com/xlDataVal01.html

--
Regards
Frank Kabel
Frankfurt, Germany

Ken said:
I have a worksheet with a cell in which I would like to make the following work:

{=SUM(IF(('ATS FY04 LANT Device Costs'!C2:C43="2F166-2")*('ATS FY04
Click to expand...
LANT Device Costs'!F2:F43=K16),'ATS FY04 LANT Device Costs'!G2:G43))}
The range C2:C43 contains simulator device names (device name appears
Click to expand...
more than once in range), range F2:F43 contains numbers (4-16) which
are the operating hours per day a device can be contracted, and range
G2:G43 are the dollar costs.
If I just enter a value (ie number 6) in cell K16, the formula works
Click to expand...
correctly and returns the value ($) for the selected device and
operating hour choice. But what I want to make happen is for K16 to be
the linked cell of a combo box allowing the user to select the
operating hours he wants from the available range for a specific
device. I have the combo box set up correctly, and if the user selects
a value from the combo box the value is assigned to cell K16 (if I go
to cell M19 and enter =K16 it will show the value the user selected
from the combo box) but the above formula will always return 0.
Any suggestions/ideas? (and Yes, the brackets are placed by using
Click to expand...
Ctrl-Shift-Enter after entering the formula)
 
Frank

Thanks, your comment about making sure the values were numbers vice 'text' got me searching other things. While the numbers in the ranges are numbers, for some reason the value the Combo Box puts in cell K16 is not. I made a change to the formula so that instead of just ...'ATS FY04 LANT Device Costs'!F2:F43=K16),... I use =VALUE(K16) This makes the formula do what I want

Ken
 
Hi Ken
glad you sorted it out :-)

--
Regards
Frank Kabel
Frankfurt, Germany

Ken said:
Frank,

Thanks, your comment about making sure the values were numbers vice
Click to expand...
'text' got me searching other things. While the numbers in the ranges
are numbers, for some reason the value the Combo Box puts in cell K16
is not. I made a change to the formula so that instead of just ...'ATS
FY04 LANT Device Costs'!F2:F43=K16),... I use =VALUE(K16) This makes
the formula do what I want.
 
Back
Top