sumif with multiple criterias

[MyStix01]

Hi,

Hope someone could help me out with my attached spreadsheet. Basically I just need to autopopulate the amounts in column H.

As you can see I have the details in columns A-D, And in order to get the value in column H, I need to

1. Lookup the account that I need using the acct code.
2. Return in column H the value of that code. The value is the amount in column D less the amount in column C.
3. You may notice that Car Expenses in column H is a combination of accounts 200.00-600.00, so I would also need the formula to be able to identify the codes in between.

Hope this makes sense and hope someone could help me out.

Thank you!

[FDibbins]

Probably the easiest way to do this (unless you want to include - (minus sign) in the depbit column, would be something like this...
=SUMIF($B$5:$B$12,H5,$D$5:$D$12)-SUMIF($B$5:$B$12,H5,$C$5:$C$12)

However, you will need to standardize your "names" so that excel knows that eg, "Fuel" and "Toll" etc are Car expenses. Same with Tax (although we could work around that)

[FDibbins]

I just noticed that you have posted the same thread here, under a different ID.
https://www.excelforum.com/excel-gen...ml#post4931718

Please do not create multiple IDS. I have closed the other thread, please continue here. Also, please explain why you need 2 IDs, and why I should not delete the other 1?

[MyStix01]

Probably the easiest way to do this (unless you want to include - (minus sign) in the depbit column, would be something like this...
=SUMIF($B$5:$B$12,H5,$D$5:$D$12)-SUMIF($B$5:$B$12,H5,$C$5:$C$12)

However, you will need to standardize your "names" so that excel knows that eg, "Fuel" and "Toll" etc are Car expenses. Same with Tax (although we could work around that)

Hi Ford,

Unfortunately I couldn't standardize the names in column A as it is how they are being reported. but for consolidation purposes, I need the value under one line item in column H. Which is why I''m thinking of using the acct codes instead of the acct name.

[AliGW]

Please answer Ford's question about having two accounts on this forum:

Please do not create multiple IDS. I have closed the other thread, please continue here. Also, please explain why you need 2 IDs, and why I should not delete the other 1?

[MyStix01]

Please answer Ford's question about having two accounts on this forum:

Hi Ali, I have explained myself privately. apologies for creating two IDs. Please do so delete the other one. Thank you.

[AliGW]

Thank you. Ford will delete the other ID - he is an administrator.

I will have a look at your file now.

[AliGW]

The account codes are text, which is a problem. Can we convert them to real numbers?

[AliGW]

Here's a possible solution with a helper column, which could be hidden:

Excel 2016 (Windows) 32 bit

	A	B	C	D	E
4	ACCT Code	ACCT Name	Debit	Credit	Helper
5	100.00	Revenue		$ 100,000.00	100
6	200.00	Fuel	$ 60.00		200
7	300.00	Replacement	$ 500.00		300
8	400.00	Maintenance	$ 1,000.00		400
9	500.00	Parking	$ 30.00		500
10	600.00	Toll	$ 200.00		600
11	700.00	Furniture	$ 120.00		700
12	800.00	Tax Payable	$ 500.00	$ 700.00	800

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	E
5	=VALUE(A5)

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	G	H	I
4			AMOUNT
5	100	Revenue	$ 100,000.00
6	200-600	Car Expenses	$ (1,790.00)
7	700	Furniture	$ (120.00)
8	800	Tax	$ 200.00

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	I
5	=SUMIFS($D$5:$D$12,$E$5:$E$12,">="&LEFT(G5,3),$E$5:$E$12,"<="&RIGHT(G5,3))-SUMIFS($C$5:$C$12,$E$5:$E$12,">="&LEFT(G5,3),$E$5:$E$12,"<="&RIGHT(G5,3))

Sheet: Sheet1

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[MyStix01]

Hi Ali,

Thank you for your help. The reason why the numbers were set to text is because some codes from my main file have decimal codes like 900.01 or 900.13. With that being the case can I still use this formula by changing the 3 to 6? and retain the text format? Or does this only works on number format?

=SUMIFS($D$5:$D$12,$E$5:$E$12,">="&LEFT(G5,3),$E$5:$E$12,"<="&RIGHT(G5,3))-SUMIFS($C$5:$C$12,$E$5:$E$12,">="&LEFT(G5,3),$E$5:$E$12,"<="&RIGHT(G5,3))

[rondeondon]

I'd go a different route, make a lookup table that links the accounts to the header you want to group them by, ie introduce a hierarchy. Most accounting systems have something like this for reporting purposes.
If you put your groupings on a separate sheet then use a vlookup or similar to return the grouping header then use sumif to get your summary reports.

Sample attached
I've highlighted in yellow where I have made changes.

If you need to have a longer account/header list I'd put it on another sheet and refer the vlookups to an entire column rather than specific range.

Ron

[AliGW]

... With that being the case can I still use this formula by changing the 3 to 6? and retain the text format? Or does this only works on number format?

Try it and see! I think that Rondeondon's solution is probably the way to go, however. Let us know if you still need help with this.

[MyStix01]

Hi, thank you for your responses. One question though, how do I change the result from having a negative value?. Thank you.

[AliGW]

What do you mean?

Maybe this:

=ABS(SUMIFS($D$5:$D$12,$E$5:$E$12,">="&LEFT(G5,3),$E$5:$E$12,"<="&RIGHT(G5,3))-SUMIFS($C$5:$C$12,$E$5:$E$12,">="&LEFT(G5,3),$E$5:$E$12,"<="&RIGHT(G5,3)))

[MyStix01]

Hi, thank you for your responses. I tried both your formulas, I included a separate sheet as masterlist. This should work now in my spreadsheet. Thank you sooooooooooooo much...

[FDibbins]

Happy to help