组合COUNT IF和VLOOK UP EXCEL [英] Combining COUNT IF AND VLOOK UP EXCEL
问题描述
我在工作簿中有多个电子表格
,我希望以下基本的 English
talk:
I have multiple spreadsheets in a workbook
and I would like the following in basic English
talk:
IF 工作表1(单元格)A3
,出现在worksheet2列B中,计算出在列b工作表2'
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次 - 我想要公式来计算这个事实,即$ code> A3 Bob smith在工作表中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进行单独的计算,然后在另一个单元格中计数/ do if statement
,例如
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)
帮助将非常感激,我很困难 - 我不确定我是否太深入或n足够了谢谢你提前
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
。 Por ejemplo:
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屋!