Combining, COUNTIF, SUMIF / IF, VLOOKUP

[CraigG]

Hi

I'm trying to combine COUNTIF, SUMIF/IF and VLOOKUP and looking for advice. It's probably easier to explain with an example...

----A-------B----------C---------D
1---ABC----qwe-------qwe-------poi

Then on another sheet...

----E-------F
2---ABC----this is where I need the formula


I have hundreds of rows in each worksheet with a name like ABC (they match like-for-like on each worksheet)

In F2 I need a formula which...
- matches the name from E2 to what's in column A
- once it's found the match, count the number of occurrences of 'qwe' and 'poi' and add them together

All names (eg, ABC) are unique and will appear only once on each worksheet.

Can anyone help?

 

[Robert Mika]

Try to follow this tip:
http://help.wugnet.com/office/SUMIF-multiple-worksheets-ftopict1157402.html

 

[T. Valko]

Hi

I'm trying to combine COUNTIF, SUMIF/IF and VLOOKUP and looking for advice. It's probably easier to explain with an example...

----A-------B----------C---------D
1---ABC----qwe-------qwe-------poi

Then on another sheet...

----E-------F
2---ABC----this is where I need the formula


I have hundreds of rows in each worksheet with a name like ABC (they match like-for-like on each worksheet)

In F2 I need a formula which...
- matches the name from E2 to what's in column A
- once it's found the match, count the number of occurrences of 'qwe' and 'poi' and add them together

All names (eg, ABC) are unique and will appear only once on each worksheet.

Can anyone help?

Maybe this...

Book1
	A	B	C	D
2	ABC	qwe	qwe	poi
3	SDF	X	qwe	X
4	FFF	poi	_	qwe
5	ABC	X	poi	X
6	GHJ	X	X	X
7	ABC	poi	poi	poi
Sheet1

=SUMPRODUCT((A2:A7="ABC")*(ISNUMBER(MATCH(B2:D7,{"qwe";"poi"},0))))