Can I use sumif and if function together

[Sarah7828]

Hi


I have the following data

Column a Column b Column c

Debit. Credit
Journal 1 - 200



I have a table as follows on the same spreadsheet

Journal 1 Sumif(column a ,“journal 1”, column b)

However, as you can see there is no value in column b. if the return value is zero, then I need to sumif column c , but the return value needs to be negative as it is a credit

So the parameters I am trying to achieve is as follows to create a formula :

If there is no value in column b for journal 1, then the formula need to return the value in column c , but a negative value ie - 200

If there is a dollar value in column b for journal 1 , then it return a positive value ie 200 as it is a debit . Therefore , column c the value will be zero

If there is not value in column b and c for journal 1 , the return value will be zero


How do I amend my sumif function to consider the above parameters?

Thank you

 

[StephenCrump]

Two ways to get what I think (not totally clear) you're asking for:

	A	B	C	D	E	F
1	Journal	DR	CR
2	100	$50.00
3	100		$20.00		Journal	Total
4	110	$15.00			100	$60.00
5	100		$35.00			$60.00
6	200	$20.00
7	100
8	200		$30.00
9	100	$65.00
10	110		$45.00
11
Sheet1

Cell Formulas
Range	Formula
F4	F4	=SUMIF(A2:A10,E4,B2:B10)-SUMIF(A2:A10,E4,C2:C10)
F5	F5	=SUMPRODUCT(B2:B10-C2:C10,--(A2:A10=E4))

 

[Sarah7828]

Two ways to get what I think (not totally clear) you're asking for:

	A	B	C	D	E	F
1	Journal	DR	CR
2	100	$50.00
3	100		$20.00		Journal	Total
4	110	$15.00			100	$60.00
5	100		$35.00			$60.00
6	200	$20.00
7	100
8	200		$30.00
9	100	$65.00
10	110		$45.00
11
Sheet1

Cell Formulas
Range	Formula
F4	F4	=SUMIF(A2:A10,E4,B2:B10)-SUMIF(A2:A10,E4,C2:C10)
F5	F5	=SUMPRODUCT(B2:B10-C2:C10,--(A2:A10=E4))

Hi Stephen

Journal 1 is unique. It doesn’t repeat . It needs to satisfy the three parameters I wrote in the thread.

 

[StephenCrump]

Ok, thanks.

But those two formulae should still work?

As should: =XLOOKUP(E4,A2:A10,B2:B10-C2:C10,0)

 

[Sarah7828]

Hi Stephen

Thank you for that

But I still need an if function I believe


I have put the data into a table using an sumif function you mentioned


Col a. Col b Col c Col c. Col d. Col e


Dr. Cr

Money out Money in Jn1 0 200


I want to use an if function

If(money out=column d, -column d, if( money in=column e, column e,””))

So money out is equal to column d, if there is a value in column d , then it should be a negative value , and if money in is column e values , and should return column e values as positive values . If there is no values In column d or e, return blank

But my IF function is not returning values

Can you help

Thank you for your help so far. Appreciate it

 

[StephenCrump]

I have put the data into a table using an sumif function you mentioned

If you click here, you can paste my layout directly into cell A1 of a blank worksheet.

I want to use an if function

If(money out=column d, -column d, if( money in=column e, column e,””))

Can you please post your layout, ideally using XL2BB, or if not, in a form we can copy, like this:

Row/column	B	C	D	E	F	G
1	Journal	DR	CR
2	100	$50.00
3	100		$20.00
4	110	$15.00
5	100		$35.00
6	200	$20.00
7	33		$4.00
8	200		$30.00
9	100	$65.00
10	110		$45.00

and show us the actual formula you're using, rather than a description of the formula.

 

[Sarah7828]

 

[Sarah7828]

So formula in column w . Thanks Stephen

 

[StephenCrump]

Will this simple formula, copied down the column, give you the results you need?

W5: =U5-T5

If not, please let us know what results you expect to see in W5, W6, W7 etc?

 

[Sarah7828]

No sorry

I need the formula to depend on the words “ money in” and “money out” . Just not sure why my IF function is not working

If money out = cell t5 , then t5 is negative , and if money in= cell u5 , u5 is positive . What happens in column w depends on whether the criteria is money out or money in