用于合并相似行但将1列中的值相加的公式 [英] Formula to combine similar rows but sum values from 1 column

问题描述

我的数据格式如下(数量在仓库之间分配)

I have data in a format like the following (qty is distributed among warehouses):

我想得到这样的输出(对各个仓库的数量求和):

I would like to get output like this (sum the qty across warehouses):

可以使用Excel或Google表格公式完成此操作吗?我不是很熟悉如何设置VBA函数，因此如果可能的话，公式将是一个很好的选择.非常感谢！

Can this be done using an Excel or Google Sheets formula? I'm not real familiar with how to setup a VBA function, so a formula would be excellent if it's possible. Many thanks!

推荐答案

将此数组公式和标准公式放在E2:F2中并填写.

Put this array formula and standard formula in E2:F2 and fill down.

'array formula in E2
=INDEX(A$2:A$99, MATCH(0, COUNTIF(E$1:E1, A$2:A$99), 0))
'standard formula in F2
=SUMIFS(C:C, A:A, E2)

google-spreadsheet

=query(A:C, "select A, sum(C) where A <> '' group by A label sum(C) 'qty'")

这篇关于用于合并相似行但将1列中的值相加的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！