Solved sumif with 2 (1 Viewer)

[lwarren1968]

I am trying to include an "&" statement to look at 2 columns within my SUMIF statement, but it's not working. Works fine if I'm only including 1 column. Any advise?

=SUMIF(Sheet1!A2:A1000,"*001*",Sheet1!D2:1000) I want to include an IF Sheet1!C2:A1000,*01*.

 

[theDBguy]

If you're trying to evaluate two cell values for your SUMIF operation, maybe you need to use an AND() operator? Just a thought...

 

[lwarren1968]

If you're trying to evaluate two cell values for your SUMIF operation, maybe you need to use an AND() operator? Just a thought...

I'm not sure how to incorporate the AND(). I'm doing something wrong. Def. not an expert.

=SUMIF(AND(Sheet1!A2:A1000,"*001*",Sheet1C2:C1000),Sheet1!D2:1000)

 

[Gasman]

Use SUMIFS?

 

[lwarren1968]

Use SUMIFS?

I'm getting an error "entered to few arguments for the function"

=SUMIFS(Sheet1!A2:A1000, Sheet1!C2:C1000,"*01*",Sheet1!D2:166)

LOC	DATE	Drawer	CASH
001	03/23/23​	00-507	0.00​
001	03/23/23​	00-768	0.00​
001	03/23/23​	00-WEB	0.00​
001	03/23/23​	01-013	719.58​
001	03/23/23​	01-014	460.90​
001	03/23/23​	01-015	1008.47​
999	03/23/23​	01-030	61.31​
004	03/23/23​	04-041	0.00​
004	03/23/23​	04-042	1156.07​
006	03/23/23​	06-061	529.22​
006	03/23/23​	06-062	884.28​

 

[Gasman]

So google the syntax for sumifs. That is what I do. If I remember correctly, the order of the parameters is different to sumif?

 

[lwarren1968]

So google the syntax for sumifs. That is what I do. If I remember correctly, the order of the parameters is different to sumif?

I honestly have been googling. I'm missing something, hence the reason I'm asking for help.

 

[Gasman]

This explains the syntax. Cannot get better than this really?

SUMIFS function - Microsoft Support

How to use the SUMIFS function in Excel, one of Excel’s math and trig functions, to add arguments that meet multiple criteria.

support.microsoft.com

and I was remembering correctly, as for SumIf the parameters are in a different order.

SUMIF function - Microsoft Support

How to use the SUMIF function in Excel to add the values in a range that meet criteria that you specify.

support.microsoft.com

From the last link

Understand the difference between SUMIF and SUMIFS.

The order of arguments differ between SUMIFS and SUMIF. In particular, the sum_range argument is the first argument in SUMIFS, but it is the third argument in SUMIF. This is a common source of problems using these functions. If you're copying and editing these similar functions, make sure you put the arguments in the correct order.

 

[arnelgp]

see this demo.
if your formula result is always = 0, then you need to Re-type the Cash value (one by one).

 

[lwarren1968]

see this demo.
if your formula result is always = 0, then you need to Re-type the Cash value (one by one).

Thank you so much. This works perfectly. I didn't think to put the calculation in first.

 

[Gasman]

Thank you so much. This works perfectly. I didn't think to put the calculation in first.

So post #8 was a waste of time?

 

[lwarren1968]

So post #8 was a waste of time?

Absolutely not. All has been helpful. I think I was in my head regarding how it should work vs how it actually works. Taking a second look this morning clarified the process. Thanks you.