将COUNTIFS与VLOOKUP结合

本文介绍了将COUNTIFS与VLOOKUP结合的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我已经看过这里的其他帖子，却找不到所需的解决方案。

I have looked through other posts on here and cannot find the solution I need.

我目前正在使用以下公式：

I am currently using this formula:

=COUNTIFS(Tracking!$F$3:$XA$3,$F1,Tracking!$F4:$XA4,"~*")

这是在计算所有与参考成就匹配的*值（这是单元格F1），但是依赖于2个工作表，以完全相同的顺序列出人员。我真正想要的是基于匹配人员ID来计算的countif。

This is counting all the * values that match to the reference "Attainment" (This is cell F1), but is relying on my 2 worksheets to have the people listed in exactly the same order. What I really want is the countif to calculate based on matching the person ID.

我有2张纸，每个人使用唯一的ID。我希望能够匹配此ID，然后运行countif，因此本质上将VLOOKUP与COUNTIFS结合在一起。我确实尝试过向COUNTIFS添加另一个准则，但这似乎不起作用。在任何一张纸上都没有重复的ID。唯一的ID定义了每一行，并且该行包含该人的大量成绩数据。

I have 2 sheets that use a unique ID for each person. I want to be able to match this ID and then run the countif, so in essence combine a VLOOKUP with the COUNTIFS. I did try adding another criteraia to the COUNTIFS but this doesn't seem to work. No IDs are repeated in either sheet. The unique ID defines each row and the row holds a very large amount of grade data for the person.

样本数据

Sample Data

推荐答案

我认为您可以在F3中使用此公式来完成

I think you could accomplish this using this formula in F3

=COUNTIF(INDIRECT("Tracking!"&MATCH(A3,Tracking!$A:$A,0)&":"&MATCH(A3,Tracking!$A:$A,0)),"*~*")

这是使用匹配的行号来计数具有的任何单元格* 在该行。

This is using the row number of the match to COUNTIFS any cells with a * in that row.