Formula Error

[CreativeRova]

Hi Excellers,

I have a formula I am using to count if an instance occurs.

=IF($D$10:$D$100="Backs",SUMPRODUCT((MOD(ROW(H10:H100)-ROW(H10),2)=0)*(H10:H100)),0)

but I am getting a #value error I can not figure out what I am doing wrong. "the search "Backs" will change on various lines but 5 different search criteria words will search the same columns.

Please help.

 

[DRSteele]

I didn't try your formula, but I suspect that you need to invoke it with CtrlShift+Enter because it looks like a formula-argument-array operation is happening. So try that.

 

[CreativeRova]

HI DRSteele,

I tried that getting a result of 0.

Also column Dhas many different texts in there. I need to return only the sum of column H where the correct word on the same row in D.

 

[DRSteele]

Please post sample data so we can conduct experiments.

 

[CreativeRova]

Example: Formula to go in D1 is to count paid only if B8 = Tops. D2 is to count Bonus if D2 = Tops etc.

	Tops	Paid
		Bonus
	Backs	Paid
		Bonus
	Combos	Paid
		Bonus
Client 1	Tops	Paid	50	50	50
		Bonus	20	20	20
Client 2	Backs	Paid	100	100	100
			30	30	30

<tbody>
</tbody>