Multi-Table Multi-Condition CountIf

  • Thread starter Thread starter JCH
  • Start date Start date
J

JCH

OK, I have worked on this for a few hours, trying every
variation of the SUMPRODUCT, COUNTIF, SUMIF functions I
could devise.

I am referencing a particular list to get my values, then
looking it up in another table and matching the value up,
then getting the corresponding value to that from the next
cell. For example

Table1: TableX:
Col1 Col1 Col2
YYZ YOW Yes
YHZ YUL No
YOW YVR Yes
YUL YHZ Yes
YVR YYZ No

I need to do a count of how many "Yes" values I have in
table one. There are a series of similar tables to table
1. Table X is simply a lookup/reference table.

I will continue to look, but my resources have come up
empty so far and I would love to get this going tonight.
Cheers,

JCH
 
JCH -- stop asking for multi condition COUNTIF :) There aren't any, it only
takes one condition, but you CAN add two countifs together...

You can use sumproduct again here, if I'm understanding correctly. You want
to have a formula running down, say, column 2 of table 1? Which will count
the # of "yes"s?

Just use SUMPRODUCT((Sheet2!A$1:A$10=A1)*(Sheet2!B$1:B$10="Yes"))

assuming that table X is on sheet 2, and YYZ (for example) is in A1 of the
current sheet.
 
Do you want to look them up and only count matches, if so try

=SUMPRODUCT((NOT(ISNA(MATCH(A1:A100,Sheet1!A1:A50,0)))*(B1:B100="Yes")))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top