PDA

View Full Version : Macro to countif multiple criteria



ioncila
03-31-2010, 08:58 AM
Hi
I have this formula to countif multiple criteria in each cell of a certain range on the attached file:
=SUMPRODUCT(($A$15:$A$20000=M$1)*($E$15:$E$20000=$H2))
Where red result matches on range (M1:AE1) and blue result matches on range (H2:H14).
This works almost fine.
My problem is when I do new entries on entrytable, excel calculates too slow the countif table. And more, It makes the file big (more than 1.5 MB).

So I thought that a vba solution could solve this issue (I have started to write the code - see the code sheet).

Any help available for this, please?

Thanks.

Bob Phillips
03-31-2010, 09:41 AM
Why don't you just pivot it, it will give you a similar layout and will be much quicker.

ioncila
03-31-2010, 01:56 PM
Why don't you just pivot it, it will give you a similar layout and will be much quicker.

Honestly, because I don't like and don't feel comfortable with pivot tables. I always try to avoid it.

But if there isn't another way to speed up the calculation...

Bob Phillips
03-31-2010, 03:22 PM
Time to get comfortable, they are so versatile, so flexible so easy, you should utilise them. They are ideal for this task.