Array function

[arnab0711]

Hi,
The formulas in c5 of snap sheet aims at counting all values >0 where model type = B5,Zone = North,Month=April.
I have tried this formula but its not quiet working
(IF((MONTH(raw!$E$4:$E$4111)=MONTH($C$3))*(Model Type(raw!$E$4:$E$1111)=Model Type($B7))*(zone(raw!$J4:$J$1111))=zone($C2))*(countif(raw!$H$5:$h$4111,">0 "))

[Paul_Hossler]

Applying what I've learned here about SumProduct, in Snap Shot C5, I array-entered this ...

[vba]
=SUMPRODUCT(($C$3=raw!$E$5:$E$1537)*('Snap shot'!$B5=raw!$F$5:$F$1537)*('Snap shot'!$C$2=raw!$J$5:$J$1537))
[/vba]

Array-entered means to Shift-Control-Enter the formula. That will add the special { } around the formula

1. However, you have on Snapshot merged cells (C3 : D3) and that can cause problems (also learned that here)

2. On Snap shot the month (C3, C15, C27) has a space at the end will will cause If tests to fail, "April" <> "April "

Have you thought about using pivot tables? I've found them to less touchy about data, and a lot easier to use and change

Paul

[arnab0711]

Nope pivot table won't serve my purpose,I would either any form of array formula to calculate it.

[Bob Phillips]

You have a very unsavoury habit of asking questions, ignoring any responses to them, them asking again.

You already asked this here, http://www.vbaexpress.com/forum/showthread.php?t=42273, and were given a solution which you never acknowledged as correct or not.

[Paul_Hossler]

Nope pivot table won't serve my purpose,I would either any form of array formula to calculate it.

Well, as far as I can tell, the SumProduct array formula would probably work.

You'll just have to be very care to make sure that all your references are always correct, and that the data (such as the "April " trailing space doesn't lead to wrong answers

Paul

[arnab0711]

I apologize will keep this in mind.