SUMIFs - simple example

[Mr2017]

Hi

I've got a table where I want to add the sum of occurrences for two different letters (see below):

A appears twice, B appears thrice and C appears 4 times.

Assuming that this data covers two columns and 4 rows from cell A1, how would I write a SUMIFS formula that captures the number of occurrences for both A and B, but not C? TIA.

[TABLE="width: 131"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Letter[/TD]
[TD]Frequency[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[shift-del]

Hi

=SUM(SUMIFS(B2:B4,A2:A4,{"A";"B"}))

 

[Mr2017]

Great, Thanks shift+del - that worked.

Is there an alternative way, though, without using the "{" brackets / parentheses?

I thought there was a simpler way?

 

[Fluff]

You could use
=SUMIF(A2:A4,"A",B2:B4)+SUMIF(A2:A4,"B",B2:B4)

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Letter[/TD]
[TD]Frequency[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]A[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]B[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]C[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

D2=SUMPRODUCT(SUMIF(A2:A4,A2:A3,B2:B4))

 

[Mr2017]

Hi Fluff and Marzio

Thank you both for your responses.

I was just wondering, is there a (simpler) way to do it using the SUMIFS (with the 'S' at the end) function?

Thanks in advance.

 

[Fluff]

Three people have now supplied different solutions.
If we knew of a "simpler" formula, don't you think that one of us would have supplied it?

 

[MARZIOTULLIO]

Another way

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Letter[/TD]
[TD]Frequency[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]A[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]B[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]C[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


D2=SUMIFS(B2:B4,A2:A4,"<>"&A4)

 

[Mr2017]

Great, thanks Marzio.

 

[Mr2017]

Hi Marzio

Quick question - do you know how I can amend your formula if a new record is added to the table?

Eg in the table below, I've added one row of data ( the letter "D" in the first column and the number "5" in the second column).

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Letter[/TD]
[TD="width: 64"]Frequency[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

I then amended your formula by adding cell A5 to the not equal clause, but it didn't work, as I guess I'd need to add it somewhere else?

I still want to sum up the data for A and B, but I'd want to be able to exclude new records added to the data. Please let me know if you want me to clarify anything.

=SUMIFS(B2:B5,A2:A5,"<>"&A4,"<>"&A5")