Countifs in VBA. Loops on criteria range

**Lifeseeker** · 12-31-2011, 02:55 PM

Hello,

I would like to have VBA generate some numbers particularly using the SUMPRODUCT...as I believe the use of VBA will expedite the process greatly....

Please see the attached.

In "Test" worksheet, you see 3 columns of value, F,G and H that need numbers to be populated.

I would like to click a button, which will then generate the numbers for me. The formula I'm currently using are in the cell formula bar, but I really need to move to a VBA approach as I don't want system to calculate using SUMPRODUCT during data entry....which is very resource intensive.

If you click on the cell formula, you will notice that for each SUMPRODUCT formula, I'm filtering on two conditions(which site and which period).

Perhaps some kind of loop would be required as well?

How do I get started?

I appreciate any help at all

Thank you

**DonkeyOte** · 12-31-2011, 05:00 PM

Why not use COUNTIFS ? Far more efficient than SUMPRODUCT (iterative by nature) especially if you wish to use in conjunction with entire column references (a no-no for SUMPRODUCT / Arrays)

**Lifeseeker** · 12-31-2011, 05:46 PM

Hi
Thanks for your reply.

I think I would still have to put countifs in VBA.

I have tested it this time using countifs instead of SUMPRODUCT directly in cell formula,but I guess it would be ideal to put in VBA and just hit a button then it will calculate for me...

I am stuck how the the syntax should go even for countifs...I need 2 loops that basically loop on the Period and Site as seen in table in worksheet "Test"

Please Login or Register  to view this content.

Are you or is anybody able to help?

Thanks

**DonkeyOte** · 12-31-2011, 06:09 PM

The overhead will have been reduced significantly c/o COUNTIFS ... SUMPRODUCT is very inefficient when used in large quantity and/or with large precedent ranges.

Given the above I am not sure I see the value in calculating the same in memory c/o sub routine. Not saying you can't (obviously) just curious as to the need / desire

**Lifeseeker** · 12-31-2011, 06:35 PM

Hi Donkey,

What I'm showing you is just the tip of the iceberg. There is a whole bunch of countifs formula that I'm trying to embed into VBA. This is only a start for me as I'm trying to learn. ( it was slow just because of the number of countifs I have been using in some other worksheets)

if you have time, I really appreciate your help on it.

Thanks