Count cells with “Apple” only in rows that matches the name

[kevkev]

I'm trying to create a spreadsheet that counts the number of a particular occurrence on a row only when it matches the name.

Cover Sheet

	Col A	Col B
Row 1	BOB	=SUMPRODUCT((General!$A:$A=Cover!A1)*((General!$B:$FZ="Apple")))
Row 2	JANE
Row 3	JOE
Row 4	JOHN

General Sheet

	Col A	Col B	Col C	...Col FZ
Row 1	JANE	Apple	Orange	Apple
Row 2	BOB	Orange	Apple	Orange
Row 3	JOHN	Apple	Apple	Apple
Row 4	JOE	Orange	Apple	Apple

So far I have this formula =SUMPRODUCT((General!$A:$A=Cover!A1)*((General!$B:$FZ="Apple"))) which works well for small data but it gives the "Excel ran out of resources while attempting to calculate one or more formulas. As a result, these formulas can not be evaluated." when applied to big data (200 rows and 160 columns. File size 97kb). The result of this formula is '1', which is what's expected. I've tried to convert "CountIf" but I can't get it to give the expected results. Help please!

 

[Fluff]

Never you whole columns in an array formula, you should always limit them to the data, or a few rows extra.
Try
=SUMPRODUCT((General!$A1:$A500=Cover!A1)*((General!$B1:$FZ500="Apple")))

 

[kevkev]

Thanks Fluff. Reducing the number of rows worked like magic. It actually skipped my mind cos when I used the whole columns and rows on CountIf, it didn't throw the "ran out of resources" error, just the formula I tried using was giving the wrong result. Can this formula be converted to a CountIf??

 

[Fluff]

Can this formula be converted to a CountIf??

You would need to use Countifs rather than Countif, but not sure it could be done. Sumproduct is probably the better option.

 

[kevkev]

Oh! Okay. I've tried both CountIf and CountIfs but I couldn't get it to work. I'll do with this SumProduct solution. Thanks again for your help!

 

[Fluff]

You're welcome & thanks for the feedback