Using Index Match between two sheets in excel

Question

I have two sheets. This is the first sheet. Named 3

And this is the second sheet. Named centralizare

I have to write in Sheet2 C5 the number of 'Id garantie' from Sheet1 if the 'Categorie' from Sheet1 is 'Done' (Sheet2 B5) and 'Status 1'from Sheet1 is 'In lucru' (Sheet 2 B4)

I tried this

=COUNT(INDEX('3'!$C$2:$C$99,MATCH(B5,'3'!A2:A99,0)))

but it returns only one number if only the 'Categorie' is Done.

And other method with errors

=MATCH('3'!C2:C99,(centralizare!C5='3'!B2:B96)*(D4='3'!F2:F96),0)
=INDEX('3'!$C$2:$C$99,MATCH(D5,'3'!C2:C99,0))
=COUNT(INDEX('3'!A2:A96, MATCH("In lucru",'3'!E2:E96)))

you are looking for =COUNTIFS('3'!$A$2:$A$99,B5,'3'!$C$2:$C$99,C$4)??? — Dirk Reichel
– Dirk Reichel, Commented Mar 19, 2016 at 13:47
oh sorry... in sheet '3' col B and D are hidden... I missed that... =COUNTIFS('3'!$A$2:$A$99,$B5,'3'!$E$2:$E$99,C$4) will be correct then :P — Dirk Reichel
– Dirk Reichel, Commented Mar 19, 2016 at 14:20

logicOnAbstractions · Accepted Answer · 2016-03-19 14:11:22Z

1

You could try an array formula with sumproduct:

=SUMPRODUCT(--IFERROR((('3'!A2:A5=centralizare!B5)),0))

Must be entered using ctrl + alt + enter. Is should appear like so in your formula bar if done right:

{=SUMPRODUCT(--IFERROR((('3'!A2:A5=centralizare!B5)),0))}

Sample workbook: https://filetea.me/t1sjZdwkvxrRxGePZmKZyB7BQ

EDIT: It also seems that you should never had "finalizat" & "wip" in the same row, if I understand your rules right. WIth the array formula, you can check for both rules & only count those that meet both rules, not just one, and somewhat check data quality this way.:

{=SUMPRODUCT(--IFERROR((('3'!A2:A5=centralizare!B5))*('3'!C2:C5=***choose a cell/range where you put a reference to finalizat/in luru instead of wip/donw***),0))}

In other words, the general format for multiple criterial is:

{=SUMPRODUCT(--IFERROR(((CRITERIA ONE)*(CRITERIA TWO)),0))}

edited Mar 19, 2016 at 14:11

answered Mar 19, 2016 at 14:03

logicOnAbstractions

2,7204 gold badges30 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using Index Match between two sheets in excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related