Excel Sum(ifs) between dates, sum month & sum with multiple criteria

One of my criteria is a word containing periods. Specifically, I'm looking for "H.O.A. Fee" and it gives some weird value. But if I change to "HOA Fee" it works fine. Is there a special way to look for periods in a formula? I thought it was odd and makes me wonder about other special characters.

Also is there a way to set the date for only the month excluding the year. I make a new spreadsheet for each year and would have to manually change the year for each formula I use this in.

Does the range criteria work for dates that are in the same column but aren't in exact order? For example if my column was 9/1, 9/3, 10/3, 9/15 - would the range criteria still pull the data from only the three September dates if thats what I chose?

Dear Madam! Can we get value by vlookup function between two dates?

Even though I have applied the same formula explained for sumifs between two dates, total displayed is wrong. What could be the problem and how to fix it?

Thank you, it helps me

Thanks a lot for sharing , it is a lot of time savings watching your videos , it is better and easier to learn from your videos than reading excel’s help! Great Job !!

Thank you!

Dear ms. Leila, we appreciate your clear logical explanations and your sweet friendly manner.

Thanks!

Thank you for such easy explanation , absolutely 100% educational.

Plz support how to make sumifs single column range and critiriya Ex
Prabha
1
2
3
Prabha total 6 should come
Dinesh
5
7
6
Dinesh total 18 .what formula should I use about like

I want the same calculation but for Standard Deviation. Can you explain how can be done. Thanks

for me in google sheets the "less or equal to" does not work with this formula "<="&EOMONTH for the last day of the month, it only sums all the other days before, so i modified it to "<"&EOMONTH(P7,0)+1

Hi Leila. I am new to excel and was trying to put a formula for my company sales report. We have above 15 products sold at various location, payment mode is either Card, Cash, Online. These product sale is monitored on weekly basis at product level at all location (individually). My company believes 1st date of every month is when the week start and ends on 7th, and from 8th begins the second week and so on. Please help me with it how do I make the same on weekly basis for over 15 products at location wise with break up card,cash,onlie. Thanks in advance.

Easy to understand. All aging balance sheet realted issues solved

Great!!!

Hi Leila,
Could you please help me figure this out? The formula is almost perfect but I noticed excel is also summing totals not in the correct range.

Here’s the formula: =IFERROR(SUMIFS('Forecast Pivot'!$D$3:$D$26, 'Forecast Pivot'!$A$3:$A$26, "EE", 'Forecast Pivot'!$B$3:$B$26, "<="&G116, 'Forecast Pivot'!$C$3:$C$26, ">="&G116), "")

In my capacity sheet, I’ve inserted this formula in order to pull the forecast totals for CW13/2023 to CW52/2024.

The formula as it is thinks 13/23 is the same as 13/24 so the totals planned on 13/23 also appears on 13/24 when it should not.

G116:CT116 is my calendar week range 13/23 to 52/24
A3:A26 is my skill (EE)
B3:B is my start date
C3:C is my end date
D3:D is my totals

Basically I want the totals in my pivot table to automatically populate in the applicable Calendar Week on my Capacity sheet. The pivot table could have overlapping dates, start date could be 13/23 and end date is 15/23

There could be another requirement on 14/23 and 16/23 and the formula should show a combined total on 14/23 and 15/23 the problem is the combined total also appear in the CW of 2024 when it shouldn’t

I love you

thanks

Hey Leila! I'm currently running my own business and I'm trying to find out some info with excel. I have a log made up on excel, which automatically logs my invoices that I also have on Excel. So the log keeps info on invoice numbers, pricing per the invoice, dates, as well as Sq. Ft. of each home. I do Deep Cleaning on homes, and I'm trying to find the average cost that I charge, based on Sq. Ft. of each home in a sq. ft. range. So how much on average do I charge for a home that ranges from 0 sq. ft, to 500 sq. ft. and then the same for 501 sq. ft. to 1000 sq. ft. If I could include a screen shot of my excel work sheet, I would. But are you able to help me out. It's kind of similar to this video, but different factors/variables. (Instead of date ranges, I'm using sq. ft. ranges, average price for those sq. ft. ranges, shown on a spread sheet, for each invoice I've billed for. Thanks!

THANK YOU IT'S TOO USEFUL ME

Can u teach same function in power bi tables not via measure or share link if you have

I did this and it works for first few cells and then it just tells me "excel ran out of resources while attempting to calculate...." Seems to be a circula issue with the formula when using for many cells? Please someone help. I hate excel man. Thanks.

AMAZINGGGGGG

1) This was exactly what I was looking for 2) This is by far the best and clearest video on this subject 3) You are simply the best...

Thank you!

I was looking for exactly this!

How would you calculate, the average role length, if you had multiple roles and within each role you had multiple contracts with different start and end dates?

Wow, you go super fast but I was able to learn the EOM function and added this to my sumifs to sum our families expenses by month. Thank you for posting this and taking my excel knowledge to the next level! I had to screen shot your formula and then blow it up so I could read it, but when I applied it to my data it worked the first time. That rarely happens:) Basic example I used to understand it: =SUMIFS(C:C,A:A,">="&E3,A:A,"<="&EOMONTH(E3,0)) C:C being values, A:A being value dates, E3 being first of each month, 0 being the current month

Thank you!

Hi Leila, lets say you had negative numbers in the revenue column (-400. -350, etc..). Is it possible to sum only the negative or the positive numbers seperatley using the first formula? (sum between dates). Thanks

Thank you so much for this! I had been looking for something like this and couldn't seem to find it. You explained it clearly and I was able to make my 2023 Balance Sheet with categories and look at overall totals as well as monthly totals. I appreciate your wisdom, thank you for sharing it.

Thank you, this saved me so much time.

I am currently working on a project that I had to averageifs the selling price between 2 dates. this video saved my ass thank you so much

Hello Leila, I am trying to use the SUMIFS formula and for some unknown reason, it is not working. I am copying the formula from your video exactly as you are writing it but it keeps showing the result as zero. It is driving me crazy.

If you do not like the IFS structure, you can also do it this way…. =sum($c$4:$c$33*($a$4:$a$33>$h$6)*($a$4:$a$$33<=$$i$6))
using a compound Boolean test (expressed by multiplication of the criteria times the values, and summing the results).

I.e. =sum( (things to be summed) * (test condition 1) * (test condition 2) ).

Only when each test condition is true does this provide the value in column C to be summed.

Better yet. - wrap the entire test in an iferror( …. ,0) test and use a table, such as:

=sum( IFERROR( Table1[Revenue] * (Table1[Date]>$H$6) * (Table1[Date]<=$I$6) ,0) )
It is helpful to include space delimiters between function sections for clarity.

The excel from you is never die 👍 Now is useful to my works Thanks 🙏 ❤.

you save my life! thank you for your videos, as a newly promoted team leader your video really helps me..

Time Saving!!! So easy to follow, and will be helpful for years. Thanks for the great video.

Thanks a lot Leila Gheraniji 🙏😀👍

You are always great

It's amazing how they manage to make simple task this hard. Absolutely no way you can figure this out by your own. Excel programmers should be fired and forced to sit down doing nothing rest of their life so they cannot ruin anything else ever again.

I CANT EVEN THANK YOU ENOUGH!! I had been struggling since March 2017 (and searching countless Excel videos) and asking several Excel experts, and nobody could start to help.. and finally I found this video! It not only fixed my issue and saved me hours of time each year, BUT when I go to save 2022's workbook as 2023, all I'll need to do is change 2 digits and the ENTIRE 2023 workbook will update!! Thank you from the bottom of my heart! ❤️ MUAH!!

Thank you helped me with my cis 200 Professor homework a real goat

4 years later, this video is still valuable.

Tqqq very much

i would like to ask you for help, that i want to change the date format in excel for our local language
there is special name of the month we want to replace with for instance instead of 01/jun/2022 i want to despply 10/ASAD/2022 can you get through it?
thanks

Thanks a lot. Solved my problem. ❤

Hi Leila
Why unable to get sum/to avenge when I try to put in additional criteria?