结合 COUNT IF 和 VLOOK UP EXCEL [英] Combining COUNT IF AND VLOOK UP EXCEL

问题描述

我在一个 工作簿 中有多个电子表格，我希望在基本的 English 谈话中包含以下内容:

I have multiple spreadsheets in a workbook and I would like the following in basic English talk:

IF worksheet1(cell)A3, 出现在'worksheet2'列B - 计算它在b列'worksheet2'中出现的次数

IF worksheet1(cell)A3, appears in 'worksheet2' column B - count how many times it appears in column b 'worksheet 2'

换句话说 - 让我们说 A3 = BOB smith - 在工作表 1 中并在工作表 2 中出现 4 次 - 我希望公式计算 A3 'Bob smith' 在工作表 2 中出现 4 次的事实，然后回来告诉我 4.

So in other words - Lets say A3 = BOB smith - in work sheet 1 and appears 4 times in worksheet 2 - I want the formula to count the fact that A3 'Bob smith' is in worksheet 2 4 times, and come back and tell me 4.

我试图进行单独的计算 - 使用 Vlookups - 然后在另一个单元格中计算/执行 if 语句例如

I have attempted to do separate calculations - with use of Vlookups - then in another cell to count/do if statement for example

=COUNTIF(VLOOKUP(A9,'To retire'!J:J,9,1))
=IF(J228=O233, 'worksheet2'!F440,0)
=VLOOKUP(A3,'worksheet2'!A:A,1,1)

非常感谢您的帮助，我被困住了 - 我不确定我是否对此研究得太深入或还不够！提前谢谢您

Help would be very much appreciated, I am very stuck - I am unsure if I am looking into this too deeply or not enough! Thank you in advance

推荐答案

当您使用 SUMPRODUCT.例子:

This is trivial when you use SUMPRODUCT. Por ejemplo:

=SUMPRODUCT((worksheet2!A:A=A3)*1)

您可以将上述公式放在单元格 B3 中，其中 A3 是您要在 worksheet2 中查找的名称.

You could put the above formula in cell B3, where A3 is the name you want to find in worksheet2.

这篇关于结合 COUNT IF 和 VLOOK UP EXCEL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！