wants SUMIFS formula help for a comlex query

0
votes

I have attached summary sheet and data sheet. Summary is where I want the formula and INCdata is the data sheet where the raw data is available.

I need a formula to sum the column J in the summary sheet for the conditions below:

1) dates in column A are falls in a calendar month

2) when column C is not equal to column D

3) should also exclude column J values when column C has values like "SUPPLIER" and "OTHER" in them.

Summary sheet that needs the formula

The formula I am using in the cell AT116 right now is =SUMIFS(INCdata!$J:$J,INCdata!$A:$A,">="&$B115,INCdata!$A:$A,"<"&$B116,INCdata!$P:$P,"1")

INCdata - data sheet

I have a column P (sheet name:INCdata) where I am checking condition 2 and 3 given above using this formula in the cell P5766 for every row (=IF($C5766="SUPPLIER",0,IF($C5766="OTHER",0,IF($C5766=$D5766,0,1)))), based on which the formula in the summary sheets yields the results for sum of J. But if there is a way to avoid using a separate formula here and include this criteria in the summary sheet formula, it would be ideal.

1
excel-formulasumifs
Hello, and welcome to SO. Did you try to solve your problem? We encourage people to try something, before posting. Have a look at these guidelines. - Daemon Painter
Thanks @Daemon Trust me I have tried so many different ways to solve this and I have solved the problem but in a way different from ideal. I wanted to know if fresh set of eyes can come up with a simple solution. - Vino

1 Answers

0
votes

Hello @Vino try this =SUMIFS(J:J,D:D,E4,E:E,D4) this will do the first part.

This would zero anything with Supplier or Other in cell C and do the above. =IF(OR(C4="Supplier",C4="Other"),0,SUMIFS(J:J,B:B,C4,C:C,B4))

I am not to sure what you have done with the dates so cant try and help there.

Hope this helps. Any questions let me know.