Sum multiple columns based on multiple conditions.

[jeetkamalarora]

	A	B	C	D
1​	Date​	Employee Code​	Cash​	Credit​
2​	01-Dec-19​	103382​	-0.5​	9.4​
3​	01-Dec-19​	103492​	-50​	50​
4​	01-Dec-19​	103586​	0​	0​
5​	01-Dec-19​	103590​	-10​	-5​
6​	01-Dec-19​	103644​	-50​	50​
7​	01-Dec-19​	103646​	-10​	-70​
8​	01-Dec-19​	103660​	-20​	-60​
9​	01-Dec-19​	103682​	-30​	-50​
10​	01-Dec-19​	103684​	-40​	-40​
11​	02-Dec-19​	103382​	-5​	94​
12​	02-Dec-19​	103492​	-500​	500​
13​	02-Dec-19​	103586​	0​	0​
14​	02-Dec-19​	103590​	-100​	-50​
15​	02-Dec-19​	103644​	-500​	500​
16​	02-Dec-19​	103646​	-100​	-700​
17​	02-Dec-19​	103660​	-200​	-600​
18​	02-Dec-19​	103682​	-300​	-500​
19​	02-Dec-19​	103684​	-400​	-400​

 

[jeetkamalarora]

Like i want sum for employee no 103382 for 1-Dec-2019 and the result should come 8.9. I used sumifs formula two times for two columns but I want to know if there is way to use one time formula only

 

[Saba Sabaratnam]

Hi

- Enter this array formula =SUM(--((C3:C20=H3)*(B3:B20=H4))*(D3:E20)) by pressing Shift + Control + Enter in cell H5.

Please see my spreadsheet layout. You will have to modify your range, depending your model layout.

Kind regards

Saba

 

[jeetkamalarora]

Great, it is working.

Thanks for the quick reply,

I have one doubt also why you put "--" because formula is working without that also.

I have also seen one thing that if any text comes in between the numbers range which have to sum then this formula is not working. Like in your formula range if I repeat header "cash" again on cell D12 then the formula is giving error while it is not in the case if I use sumifs

 

[Saba Sabaratnam]

Hi

-- (two minus sings) are to convert 'False / True', to 0/1 so that they can multiplied by number column. There are many ways to convert this boolean into numbers. The most efficient way is to use -- when you have a large financial model with hundreds of formulas.

It worked without -- in my formula because there are two criteria. When False / True of each criterion is multiplied by the other one, it will convert them into 0/1s.

You can use the following array formula to solve any errors:

=SUM(IFERROR(((C1:C20=H3)*(B1:B20=H4))*(D1:E20),0))

Kind regards

Saba