• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMIFS for matching IDs between two time periods

W

wollyka

Member
Hi
I have an file containing data about the availability of apartments in buildings at different periods of the year e.g. H1 2012, H2 2012, H1 2013 etc.. Each building has an unique id attached to it. Buildings are constantly being added and some buildings are removed when they don't have any available apartments or their data was not captured during that specific period (no reponse)

I would like to calculate the difference between the total number of available apartments between 2 periods (sold apartments) e.g. H1 2012 and H2 2012 but only for buildings that appear in those both time periods.
How can it be done? Thanks in advance
 

Attachments

  • Example Apartments.xlsx
    18.6 KB · Views: 7
I have written some formula in Sum if. Just check whether it meets your criteria or not.
 

Attachments

  • Example Apartments.xlsx
    19.1 KB · Views: 2
Thanks for taking your time, but it is not as simple as that :) Smallman was right, i should have added an example (which you can find it attached).
I want to sum the available apartments that have same IDs between H1 2012 and H2 2012 (this is just an example, the data set is large and contains thousands of records with different dates, I want to be always to sum the available apartments that have matching IDs between any two consecutive dates).
Again thanks
 

Attachments

  • Example Apartments.xlsx
    26.4 KB · Views: 3
Ok

Firstly some housekeeping
In N5 change "H1 " to "H1" without the space

Secondly in G2:
=IFERROR(AND(MATCH(A2&$O$4&$N$6,$A$2:$A$343&$D$2:$D$343&$E$2:$E$343,0)>0,MATCH(A2&$O$4&$N$5,$A$2:$A$343&$D$2:$D$343&$E$2:$E$343,0)>0),FALSE) Ctrl+Shift+Enter
Copy G2 down to the bottom of your data

O5: =SUMIFS($C$2:$C$343,$D$2:$D$343,O4,$E$2:$E$343,N5,$G$2:$G$343,TRUE)
O6: =SUMIFS($C$2:$C$343,$D$2:$D$343,O4,$E$2:$E$343,N6,$G$2:$G$343,TRUE)

or see attached file:

Enjoy
 

Attachments

  • Example Apartments Hui.xlsx
    35.6 KB · Views: 4
Hi
Thank you all for taking the time.
I took all the IDs from H1 and then from H2. I deleted the IDs that don't appear in both H1 and H2. Then I added up the available apartments for each period that match those IDs.
 
Hi Hui
Thank you very much, I will have a look on it when I have access to my PC and test it with the real data :). Hopefully I won't have any further questions.
Let me add that I am glad I found this forum. You were all very helpful those couple of days :)
 
Thanks @Hui for making the picture clear.

See this file including Pivot and formula solution. There are some dynamic named range also. Please refer sheet2.

Regards,
 

Attachments

  • Example Apartments.xlsx
    38.1 KB · Views: 5
Somendra Misra said:
Thanks @Hui for making the picture clear.

See this file including Pivot and formula solution. There are some dynamic named range also. Please refer sheet2.

Regards,
Click to expand...
Thanks for the other alternative. But it seems i have a question. Let's say i want to calculate the sum between H2 2011 and H1 2012, how can i do it using your method?
Thanks again
 
Yes, it is quite scary :) and works very well.
Both solutions by Hui and SM work as expected. Thank you both, your Excel skills are amazing.
 
Ok, It should be the last question. If i added 3 columns "District" "Region" and "Sizes" to the table, how can i modify the pivot table? i know i am pushing it to the max!!
Hui's formula can be modified easily to reflect those changes, howeverm since it is an array formula it takes too much time to run when i have more than 30000 records (at least 3 min)
Thanks
 

Attachments

  • Example Apartments (2).xlsx
    45.5 KB · Views: 2
wollyka said:
Ok, It should be the last question. If i added 3 columns "District" "Region" and "Sizes" to the table, how can i modify the pivot table? i know i am pushing it to the max!!
Hui's formula can be modified easily to reflect those changes, howeverm since it is an array formula it takes too much time to run when i have more than 30000 records (at least 3 min)
Thanks
Click to expand...

Can you elaborate how you output will look like:

Say for District- X, Region- Antelias, Sizes- Large What result do you expect?

And should you want dropdown for district, region and sizes also? So that you will select all the values from different dropdowns and you will get the result?

Regards,
 
Hi
The regions, areas and sizes should have drop-down. And for areas and sizes, they should include a additional value saying "all"
If one selects district X region Antelias sizes large between 2 periods, it should add up all available apartments that are located in that region that have a large size with IDs that occur in both time period.
If one selects district X region Antelias sizes large between 2 periods, it should add up all available apartments regardless of the size with IDs that occur in both time period.
If one selects district X, region All sizes large between 2 periods, it should add up all available apartments located in that district and are large that occur in both time period. Etc..
The drop-downs don't have to be cascading .
I hope I made it clear and not more complicated!
Thanks again for taking your time
 
Hi
Sorry, i forgot to attach the file with the example (Since it was Sunday i was posting using my phone!) and i cannot edit the above post so i am adding it here.
 

Attachments

  • Example Apartments (3).xlsx
    48.1 KB · Views: 1
wollyka said:
Hi
Sorry, i forgot to attach the file with the example (Since it was Sunday i was posting using my phone!) and i cannot edit the above post so i am adding it here.
Click to expand...

Hi,

See if this file meets your requirement.

Regards,
 

Attachments

  • Example Apartments (2).xlsx
    53.3 KB · Views: 2
Hi,
You are a genius, thank you very much.
One small thing, is it possible to include an option called "All" for sizes and Regions? If one selects "All" for sizes, it will add up the all apartments (disregarding the sizes). And if you select All for region, it will only look at district and disregard the region?
Edit: If i understood right, i only have to change this part
Code: 
($M$9=District_PT)*($N$9=Region_PT)*($O$9=SIZE_PT))
and use some IF statements?
 
Last edited:
Hi
I think it worked by adding some IF statements in Q9
Code: 
=IF(OR($O$9="All",ISBLANK($O$9)),IF(OR($N$9="All",ISBLANK($N$9)),SUMPRODUCT(H1_Range*(NOT(ISBLANK(H1_Range)))*(NOT(ISBLANK(H2_Range)))),SUMPRODUCT(H1_Range*(NOT(ISBLANK(H1_Range)))*(NOT(ISBLANK(H2_Range)))*($M$9=District_PT)*($N$9=Region_PT))),IF(OR($N$9="All",ISBLANK($N$9)),SUMPRODUCT(H1_Range*(NOT(ISBLANK(H1_Range)))*(NOT(ISBLANK(H2_Range)))*($M$9=District_PT)*($O$9=SIZE_PT)),SUMPRODUCT(H1_Range*(NOT(ISBLANK(H1_Range)))*(NOT(ISBLANK(H2_Range)))*($M$9=District_PT)*($N$9=Region_PT)*($O$9=SIZE_PT))))
and in R9
Code: 
=IF(OR($O$9="All",ISBLANK($O$9)),IF(OR($N$9="All",ISBLANK($N$9)),SUMPRODUCT(H2_Range*(NOT(ISBLANK(H1_Range)))*(NOT(ISBLANK(H2_Range)))),SUMPRODUCT(H2_Range*(NOT(ISBLANK(H1_Range)))*(NOT(ISBLANK(H2_Range)))*($M$9=District_PT)*($N$9=Region_PT))),IF(OR($N$9="All",ISBLANK($N$9)),SUMPRODUCT(H2_Range*(NOT(ISBLANK(H1_Range)))*(NOT(ISBLANK(H2_Range)))*($M$9=District_PT)*($O$9=SIZE_PT)),SUMPRODUCT(H2_Range*(NOT(ISBLANK(H1_Range)))*(NOT(ISBLANK(H2_Range)))*($M$9=District_PT)*($N$9=Region_PT)*($O$9=SIZE_PT))))

Is there any other alternative?
Thanks all
 
Exactly :)
I didn't know that you can use wildcards with SUMIFS function! You sir are a godsend :)
How much time did it take you to learn all of this? :)
Thank you again
 
Thanks for the kind words. Just for information all IF & IFS function can use wild cards (SUMIF,SUMIFS,COUNTIF,COUNTIFS,AVERAGEIF,AVERAGEIFS) even some other function which support wild cards are MATCH, SEARCH.

How much time did it take you to learn all of this? :)
Click to expand...

Well personally I feel I do not know much in Excel, all I do is, to try attempting the problem to find solutions.

All the Best with the Learning.

Regards,
 
Sorry for reviving an old thread, but in the attached file (the same file uploaded by Somendra), how can modify the formula in cell Q9 so that it sums only the values available in H1_Range that are higher than the values available in H2_Range? (leaving the same other criteria intact)
Thanks
Edit: i changed the ranges to H1 and H2
 

Attachments

  • Example Apartments (2) (3).xlsx
    53.3 KB · Views: 1
Last edited:
You must log in or register to reply here.
Back
Top