SumIfs with multiple criteria using VBA

[newbi004]

Hi all

I have the following code to sum cost in column J if the following criteria is met:

1 If the specified GLCode is in column P
2 If the specified start date (FromDate) is in column L
3 If the specified end date (ToDate) is also in colmn L
4 If the specifiedfile name (in this instance MasterFile_00) is in column Z

My problem is that I need increase the number of criteria 4 to about 20 files to check (MasterFile_12, MasterFile_19, MasterFile_34 and so on).

How could I amend the code to check occurance of these file names for inculding in the sumifs formula?

Code :

Please Login or Register  to view this content.

Many thanks

[oeldere]

What would you like to achieve?

You want to count between differant workbooks (not sheets) on a specified criteria.

If so, isn't it an option to make a summery workbook with all the values in it (on 1 sheet), and after that use the formula's to get your result?

[newbi004]

oeldere

Thank you for your interest.

I have a summary workbook which has all the data.

The formula is trying to sum all money spent by different departments (MasterFile_00, MasterFile_12 etc) for different cost centres (GLCode).

When I tried the previous code I sent, it gave me the correct answer for money spent by department MasterFile_00. However, I want to check all money spent by departments MasterFile_00 and MasterFile_13 and tried this code which gave me a zero answer:

Please Login or Register  to view this content.

How can I sumif with more cost centres. Maybe sumifs is not the right way?

Thanks

[oeldere]

If the layout (format) of the data is the same on all the sheets; I would make an summary sheet and after that make an pivot table for your needs.

You get better help if you add an excel file, without confidentional information.

Please also add the desired (expected) result.

[newbi004]

Hi again oeldere

I attach a sample worksheet which gives the desired result when I use standard formula in cells L37 and L38.

I summed the sumifs, so maybe this is the way to do in VBA? The problem is that there will be up to 20 sumifs because there are 20 different Cost Centres to deal with! Would there be a simple way to do multiple sumifs rather than write each out 20 times? (...or should this be another thread started?)

Thanks

[Debraj Roy]

Hi Newbi004!

Try This...

Please Login or Register  to view this content.

In case of VBA.. I hope you will able to adjust accordingly with Worksheetfunction, and Array("MasterFile_00","MasterFile_13",...)

[oeldere]

Maybe like this (privot table).

See the attached file.

[Debraj Roy]

BTW.. Please find the attached.. SumIfs problem.xlsx

I have used a Helper Column..
provide all Desired Cost Center there.. and in Helper column use .. COUNTIF, if any match found..

Now only use if cost-CenterHelper = 1 to get desired result..

hope it helps..

[newbi004]

Hi Debraj Roy...your formula works great in a worksheet, but I can't seem to adapt that to VBA. How can I do that? I know you have used arrays, but I do not know how to put it in VBA. Could you advise?

[newbi004]

Thank you both for your help

I used this code to get what I wanted, but will have to repeat it many times with other cost centres to capture all the data:

Please Login or Register  to view this content.

Debraj Roy, your formulae has increased my knowledge in using arrays. Thanks

oeldere, thanks for showing how to create a pivot table using VBA. I will probably use that approach rather than using my code.

Cheers

[Debraj Roy]

Hi Newbi004!

If you are using VBA.. why should you care of WorkSheetFunction...

Instead try this...

Please Login or Register  to view this content.

Hope now you can manage the DatePart..

Let us know if anything else required..

[newbi004]

Hi again Debraj

I changed the code to your suggested one:

Please Login or Register  to view this content.

but it is giving a Type Mismatch error. Have I missed some quotes in this?

Cheers

[Debraj Roy]

Hi Newbi004..

Can you please upload you complete code, so that I dont have to add
With Sheet1,
Set FromDate
Set ToDate
Set GlCode

etc..

BTW.. In case of Evaluate..
No need to add Range( just write L:L and try to replace all (") double quote within Evaluate Function to Double Double Quote ("")

[newbi004]

Hi Debraj. I am a newbie when it comes to VBA coding, so haven't used Set FromDate etc. I have done it the long winded way! The full code for that part is:

Please Login or Register  to view this content.

I amended the code as you suggested, but it doesn't work. I am obviously missing something.

Thanks

[Debraj Roy]

Hi Newbi004..

Try This one...

Please Login or Register  to view this content.