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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Tops[/TD]
[TD]Paid[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Bonus[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Backs[/TD]
[TD]Paid[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Bonus[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Combos[/TD]
[TD]Paid[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Bonus[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client 1[/TD]
[TD]Tops[/TD]
[TD]Paid[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Bonus[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Client 2[/TD]
[TD]Backs[/TD]
[TD]Paid[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]