Help with SUMIFS

[LeFoah]

Heya!

New to these forums, and registered to ask a question about SUMIFS.

I'm working on a some bookkeeping sheets, with a final sheet that acts as a bookend for the year. The bookend is designed something like this:

Sales

Service type 1 ..... X for October ..... X for November

Service type 2 ..... X for October ..... X for November

Service type 3 ..... X for October ..... X for November

Where X is where my SUMIFS function is gonna be. X is also specific for a month, say this October.

My bookkeeping sheet for income and gains is not separated into different months, but they are marked with dates.

Row 5 has the date in column A, the type of service in column F, and the profit in column H.

With that said, I want the SUMIFS function to check if column A, in this example, falls between 2013-10-01 and 2013-10-31 (Swedish standard for dates), and if it is indeed Service Type 1. If these are true, then the profit from the H column will be added. This action repeats in the interval of rows 5-105.

My current formula, which doesn't work properly, is as follows:


=SUMIFS(Intäkter!H5:H105; Intäkter!A5:A105; "=>2013-10-01"; Intäkter!A5:A105; "=<2013-10-31")

So far I've only tried to make the formula work for dates, regardless of the type of service. "INTÄKTER!" is the swedish word for income, and the name of the worksheet.


I'm hesitant to upload the file as it contains some personal information right now, but if this didn't make any sense I can spend some time recreating a similar document so that you can fully understand the problem.

PS: I'm on Office 2013, and I need the solution to be backwards compatible with 2007 and 2010.

[LeFoah]

In a furious troubleshooting spree I found a workaround, which may not be the optimal way to do things, but it worked for me:

=SUMIFS(Intäkter!H5:H105; Intäkter!A5:A105; ">2013-09-30"; Intäkter!A5:A105; "<2013-11-01"; Intäkter!D5105; "=Översättning SVE-ENG")

I removed the "equal or greater" and "equal or less" with "greater" or "lesser", and moved the dates to the last of the previous month, and the first of the following month. And then I could add my 3rd criteria that checked the type of service and it all worked flawlessly. Such a relief In case someone has similar issues I thought I'd let the thread be and add this, instead of editing and removing the original post.

[Pecoflyer]

Hej LeFoah

your solution looks ok and thank you for sharing it
You can shorten it a bit using sumproduct like this for october

=SUMPRODUCT((Intäkter!H5:H105)*( MONTH(Intäkter!A5:A105)=10)*Intäkter!D5:D105="Översättning SVE-ENG")

or replace 10 with MONTH(your_ref)

I find sumproduct more flexible than SUMIFS, but maybe it's slower