Excel 2007 : Vlookup to count matches in two columns

**notwen** · 06-08-2011, 11:37 AM

Hi,

I have attached a simple spreadsheet all about the colour of toys. If someone could explain to me how to create the formula to summarise (on sheet 1) the number of times a certain toy is a certain colour (from sheet 2) using (I think) VLOOKUP, I will be able to solve my more complicated spreadsheet.

It would make me very happy! I'm not having any luck on my own.

Thank you,
Notwen

**NBVC** · 06-08-2011, 11:51 AM

Try, in B3:

=COUNTIFS('toy list'!$A$2:$A$13,$A3,'toy list'!$B$2:$B$13,B$2)

copied down and across the columns

**notwen** · 06-09-2011, 11:21 AM

Thank you, NBVC. That worked!

Um, however, I realise that my 'more complicated' spreadsheet can't be solved with the COUNTIFS. I've attached a modified spreadsheet. I can't reorder a sheet so I don't think that VLOOKUP could be used. Can you help again, please?

Thanks,
Notwen

**NBVC** · 06-09-2011, 11:29 AM

Does this work for you?

=SUMIF(summary!$A$3:$A$5,$A2,INDEX(summary!$C$3:$E$5,0,MATCH(B$1,summary!$C$2:$E$2,0)))

This assumes the red, yellow, blue table headers in both sheets may not be in the same order... otherwise you can just use:

=SUMIF(summary!$A$3:$A$5,$A2,summary!$C$3:$C$5)

**notwen** · 06-10-2011, 06:29 AM

Thanks, NBVC!

I used the simpler version of SUMIF() and it worked fine. I applied the idea to my more complicated spreadsheet and that, too, worked fine.

Thank you so much!

I've attached the finished spreadsheet in case someone wants to look at it.

Cheers