根据具体标准计算Excel中的多个项目 [英] Counting Multiple Items in Excel Based on Specific Criteria

问题描述

如果我按照以下解释，最好：

我有一个电子表格，其中有三列：

产品名称
供应商

<有些厂商有同样的产品。

我需要计算供应商有多少个产品名称相同。

所有帮助都是非常感激的。

谢谢，

user1114330

< img src =https://i.stack.imgur.com/5eAg0.jpgalt =示例数据>

解决方案

p>在单元格C1中粘贴此公式

  = SUMPRODUCT（（$ A $ 1：$ A $ 10 = A1）*（$ B $ 1：$ B $ 10 = B1））
  

SNAPSHOT

但是，如果您还想要VBA，请参阅此

  Option Explicit 
 
 Sub Sample（）
 Dim ws As Worksheet 
 Dim lrow As Long 
 
设置ws =表格（Sheet1）
 
用ws 
 lrow = .Range（A& .Rows.Count）.End（xlUp）.Row 
 .Range（C1：C& lrow）.Formula == SUMPRODUCT（（$ A $ 1： $ A $& lrow& _ 
= A1）*（$ B $ 1：$ B $& lrow&= B1））
结束
结束Sub 
  

I looked through other questions and couldn't find what I needed.

Best if I explain as followed:

I have a spreadsheet that has three columns in it:

Product Name Vendor

Some vendors have the same product.

I need to count how many Product Names are identical by vendor.

All help is most appreciated.

Thank you,

user1114330

解决方案

Paste this formula in Cell C1

 =SUMPRODUCT(($A$1:$A$10=A1)*($B$1:$B$10=B1))

SNAPSHOT

However if you still want VBA, see this

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lrow As Long

    Set ws = Sheets("Sheet1")

    With ws
        lrow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("C1:C" & lrow).Formula = "=SUMPRODUCT(($A$1:$A$" & lrow & _
                                        "=A1)*($B$1:$B$" & lrow & "=B1))"
    End With
End Sub

这篇关于根据具体标准计算Excel中的多个项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！