How to Remember SUMIF and SUMIFS Formulas

[bencar]

Hi guys,

I get confused as to the syntax between SUMIF and SUMIFS. Do you know a way you guys can remember each syntax without getting confused one with the other?

 

[Marcelo Branco]

In SUMIFS the first argument is the Sum_range, followed by pairs of Criteria_range,Criteria.
On the other hand, in SUMIF the Sum_range is the last argument (the 3rd)

M.

 

[dave2018]

You can always use the tool tip that pops up when u type the formula:. Sumif takes a row (or column) of data

#VALUE!

 

[bencar]

In SUMIFS the first argument is the Sum_range, followed by pairs of Criteria_range,Criteria.
On the other hand, in SUMIF the Sum_range is the last argument (the 3rd)

M.

Thanks,, helps to remember that!

 

[dave2018]

I thought u were asking about how they work...

 

[Marcelo Branco]

Thanks,, helps to remember that!

You are welcome.

Yes, we can say in SUMIFS the S at the end "pulls" the Sum_range to the first argument.

M.

 

[bencar]

You are welcome.

Yes, we can say in SUMIFS the S at the end "pulls" the Sum_range to the first argument.

M.

Ahh... that will come in handy big time.. thanks!

 

[thisoldman]

You can get along only knowing SUMIFS. It works for a single criteria range, =SUMIFS(A1:A10, B1:B10, TRUE).

 

[bencar]

You can get along only knowing SUMIFS. It works for a single criteria range, =SUMIFS(A1:A10, B1:B10, TRUE).

Could you give me a small example with data?

 

[dave2018]

Excel 2013/2016
	A	B	C	D	E
2	8	West	Arthur		Replace this cell with a formula to sum the values in column A that are greater or equal to 5, Are sold in the West, and are sold by Fred
3	2	East	Jonathen
4	1	South	Frank
5	3	East	Fred
6	10	West	Frank
7	2	East	Jonathen
8	8	East	Fred
9	10	West	Arthur
10	6	East	Jonathen
11	2	West	Fred
12	7	East	Jonathen
13	9	East	Jay
14	1	West	Fred
15	1	North	Frank
16	8	South	Jonathen
17	10	North	Jonathen
18	3	East	Frank
19	9	North	Jonathen
20	5	North	Frank
21	7	West	Frank
22	9	South	Fred
23	1	North	Jay
24	6	South	Jay
25	1	East	Fred
26	7	East	Jay
27	3	West	Jay
28	10	West	Jonathen
29	7	East	Jay
30	4	East	Jay
31	7	South	Frank
32	10	West	Jonathen
33	4	South	Frank
34	10	West	Frank
35	4	West	Arthur
36	6	South	Arthur
37	1	South	Fred
38	10	West	Jonathen
39	6	North	Arthur
40	9	North	Frank
41	8	East	Arthur
42	10	West	Arthur
43	1	West	Jay
44	9	East	Jonathen
45	5	North	Arthur
46	2	South	Frank
47	3	North	Jay
48	7	South	Arthur
49	2	East	Jonathen
50	1	South	Arthur
51	9	North	Fred
52	5	North	Frank
53	8	South	Fred
54	7	West	Fred
55	3	East	Frank
56	2	West	Frank
57	8	South	Jay
58	8	East	Jonathen
59	2	West	Arthur
60	2	South	Frank
61	5	North	Arthur
62	9	North	Arthur
63	8	West	Arthur
64	5	West	Fred
65	3	North	Frank
66	9	South	Fred
67	3	North	Arthur
68	5	South	Jonathen
69	9	South	Jonathen
70	10	North	Jonathen
71	6	North	Fred
72	1	South	Jay
73	9	East	Jay
74	2	West	Jonathen
75	4	West	Frank
76	8	North	Frank
77	8	East	Jonathen
78	6	North	Jonathen
79	8	East	Jay
80	5	North	Frank
81	1	West	Jay
82	2	North	Jay
83	9	West	Jay
84	1	North	Jay
85	4	North	Fred
86	7	South	Arthur
87	9	South	Jonathen
88	5	East	Jonathen
89	6	North	Arthur
90	6	West	Fred
91	7	East	Fred
92	7	North	Jonathen
93	8	North	Frank
94	7	West	Arthur
95	1	North	Fred
96	9	East	Frank
97	9	West	Jay
98	8	South	Jay
99	5	North	Jonathen
100	10	East	Jonathen
Sheet4