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.

Letter	Frequency
A	2
B	3
C	4

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[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]

	A​	B​	C​	D​
1​	Letter	Frequency
2​	A	2​		5​
3​	B	3​
4​	C	4​

<tbody>
</tbody>

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

	A​	B​	C​	D​
1​	Letter	Frequency
2​	A	2​		5​
3​	B	3​
4​	C	4​

<tbody>
</tbody>

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).

Letter	Frequency
A	2
B	3
C	4
D	5

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

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")