SUM IF CSE STATEMENT

[excelrory]

I have developed CSE SumIf statements that refer to data in an excel table. For example,

An excel table located on the company server named LSDETAILS houses accounts receivable information for customers. This data is displayed in a columnar fashion (DIVISION, MONTH, YEAR, CUSTOMER, DOLLARS).

In a seperate excel workbook I have developed a report, which calculates customer A/R balances based on a user selecting the desired month, year, division, and customer. I have created range names that refer to the data in the LSDETAILS file. For example the range name CUSTOMER refers to column D of the LSDETAILS file.

My report formulas look like the following =ABS(IF(SUM(IF((division=$D$2)*(customer=$B8)*(month=$A$5)*(year=$B$5)*(dollars>0),dollars))<0,0,SUM(IF((division=$D$2)*(customer=$B8)*(month=$A$5)*(year=$B$5)*(dollars>0),dollars))))

This formula at one point was working, but all of a sudden it only calculates to zero. the only way I can get a value is if alter my formula to simply read =dollars (this gives me the dollar value for the cell I happen to be on, ie. if the = dollars formula was entered in row 3 then it would give me row 3's dollar value from the LSDETAILS file. This confirms that my range name references are working currectly, which leads me to believe that there is something wrong with the sumif CSE formula -- it cant handle multiple criteria or looking outside the current workbook for reference data. I'm at a complete loss, I dont understand why the formulas doesnt work anymore when HONESTLY nothing has changed in the formula or the data. Is this an excel glitch? I WOULD GREATLY APPRECIATE ANY HELP ANYONE CAN OFFER, AS IM COMPLETELY FRUSTRATED

Regards,
excelrory

 

[Aladin Akyurek]

Are you trying to conditionally sum only positive values in dollars?

 

[excelrory]

in the example i sent that would be true. basically my data table houses both positive and negative numbers. the formula i sent is grabing only the positive numbers. my report shows both debits and credits (accounting info). positive numbers are debits while negative numbers are credits. I dont believe this would be an issue as this formula has worked in the past.

i greatly appreciate your prompt response maybe we can work together to figure this one out.

thanks again
excelrory

 

[MartinK]

Hello,
wouldn't a matrix formula solve your problem?
=(division=E11)*(customer=F11)*(month=G11)*(year=H11)*(dollars>0)*dollars

You can add SUM before if there are more rows that match the criteria, or ABS, but you do not need it as you filter out negative figures by dollars>0.

Your formula is close but I really couldn't count the brackets. Also I believe there should be "*" instead of "," before "dollars".

HTH

Martin

 

[excelrory]

the formula isnt the problem. for example, if i simplify my formula to read:
=SUM(IF((division=$D$2)*(customer=$B8)*(month=$A$5)*(year=$B$5),dollars))

I still dont get the correct value (gives me "0"). The * instead of "," before dollars gives me an invalid formula.

It would seem my real problem is creating a CSE sumif statement that is referencing data outside the current workbook.

once again appreciate everyones help thus far

excelrory

 

[MartinK]

Ooops, i overlooked the IF statement. Of course it gives an invalid formula.

I do not know how your references to the other workbook look like, but INDIRECT reference does not work with closed workbooks. Do you get the same error when having the source workbook open?

I am afraid that's all advice I can give to you.

regards
Martin

 

[Aladin Akyurek]

in the example i sent that would be true. basically my data table houses both positive and negative numbers. the formula i sent is grabing only the positive numbers. my report shows both debits and credits (accounting info). positive numbers are debits while negative numbers are credits. I dont believe this would be an issue as this formula has worked in the past.

i greatly appreciate your prompt response maybe we can work together to figure this one out.

thanks again
excelrory

Confirmed with control+shift+enter instead of just with enter:

=SUM(IF((division=$D$2)*(customer=$B8)*(month=$A$5)*(year=$B$5)*(dollars>0),dollars))

would suffice to sum positive values in dollars. Reversing the conditional

dollars > 0

to:

dollars < 0

would sum the negative values.

If you get 0 as result, then maybe the dollars range is text-formatted. Do an ISNUMBER test on the values in that range to see whether they are true numbers.

BTW, What is the exact range dollars refers to? And why do you have two separate ranges for months and years if there is a dates range?