Multiple criteria on sum fuction not working

theLEARNER1991

New Member
Joined
Mar 22, 2014
Messages
15
Hello friends,
The sheet which I have created has got following columns for data entry.
column a date
column b particulars
column c amount
column c category (5 categories in drop down menu are are income & commission, expenses, expenses reimbursed, loss, loss recovered)
column d funds allocated
cell j3 contains start of assessment date
cell k3 contains end of assessment date
in cell n3 I want to compute sum of expenses between assessment date which should exclude expenses reimbursed.
Formula which I am using is giving error, formula used in n3 is
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>=(SUMIFS(C:C,A:A,">="&J3,A:A"<="&K3,D:D="EXPENSES")-SUMIFS(C:C,A:A,">="&J3,A:A"<="&K3,D:D="EXPENSES REIMBURSED"))
I even tried to compute both expenses and expenses reimbursed in different cell but still i got error
formula used to compute expenses is =SUMIFS(C:C,A:A,">="&J3,A:A"<="&K3,D:D="EXPENSES")

Your help would be appreciated

Thanks
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
theLEARNER1991, Good afternoon.

Try to use:

=(SUMIFS(C:C,A:A,">="&J3,A:A,"<="&K3,D:D,"EXPENSES")-SUMIFS(C:C,A:A,">="&J3,A:A,"<="&K3,D:D,"EXPENSES REIMBURSED"))

Please, tell us if it worked as desired.
I hope it helps.

 
Upvote 0
theLEARNER1991, Good afternoon.

Whitout an example it's very hard to help you conclusively.

Try to save your file at a free site, www.sendspace.com and put a link to download here.

I hope it helps.
 
Upvote 0
This should work...

=SUM(SUMIFS(C:C,A:A,">="&J3,A:A"<="&K3,D:D,{"EXPENSES","EXPENSES REIMBURSED"}),{1,-1})

If not, what result do you get?
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top