Skip to Content
0
Former Member
Jan 04, 2008 at 07:28 PM

Combining Count and If into an array (CSE) formula for lookup

27 Views

Post Author: Joshua Leath

CA Forum: Xcelsius and Live Office

Ok, the title sounds a tad confusing, but here is what I'm trying to do. Currently in Excel, I have a multitude of data entered on a daily basis into a table. The formula I have will look up data based on a clients name, and will return a count of errors, or sum of cost, etc... based of 4 criteria (date range, being two criteria, name of client, and catagory of error). The formula looks as follows:

{=COUNT(IF('New Format'!$J$3:$J$30=Sheet3!$B109,IF('New Format'!$I$3:$I$30=Sheet3!C107,IF('New Format'!$A$3:$A$30<Sheet3!$K$31,IF('New Format'!$A$3:$A$30>=Sheet3!$K$30,COUNT('New Format'!$J$3:$J$30),"")))))}

This is identical to the SUM formulas, except COUNT is changed to SUM

It operates perfectly in excel...type in the clients name and all the data populates for each month. However, if I create a list in Xcelsius and have it input the name into the spreadsheet, I do not get any data returned....the charts and gauges stay blank. I verified that I have the list input as label. I don't see anything wrong with the formula, as it operates perfect in Excel. Do Xcelsius not support what I'm trying to do? If not, how would I build the formula?