Need help with SUMIFS formula

[leopardhawk]

Hello, I have a SUMIFS formula that works just fine but I need to modify it so it will calculate some additional information that I need.

This is the formula that works... =SUMIFS(bookings!V:V,bookings!D:D,">="&DATE(2009,9,1),bookings!D:D,"<="&DATE(2009,9,30),bookings!B:B,"<>cancelled")

The result is that it sums any dollar amounts in column V during the month of September, 2009 as long as column B does not contain the word 'cancelled'.

What I need the new formula to do is similar but it has to subtract column Y from column W and return a result. The other criteria, that it be in the month of September, 2009 and column B not have the word 'cancelled' in it, still apply.

I would really appreciate any help. All I get is an error no matter what I do.

Thanks!

 

[mvptomlinson]

Hi Leopard, if I understand what you're saying, the following array formula will subtract every column Y value from each corresponding column W value, and then sum all the differences:

=SUM(W2:W4-Y2:Y4)

Press CTRL+SHIFT+ENTER, not just ENTER and Excel will automatically add braces around the formula.

You can add this to the end of your current formula if you need to subtract that value from the SUMIFS value, e.g.

=YOUR_FORMULA-SUM(W2:W4-Y2:Y4)

The whole formula needs to be array entered in that case.

 

[T. Valko]

Hello, I have a SUMIFS formula that works just fine but I need to modify it so it will calculate some additional information that I need.

This is the formula that works... =SUMIFS(bookings!V:V,bookings!D:D,">="&DATE(2009,9,1),bookings!D:D,"<="&DATE(2009,9,30),bookings!B:B,"<>cancelled")

The result is that it sums any dollar amounts in column V during the month of September, 2009 as long as column B does not contain the word 'cancelled'.

What I need the new formula to do is similar but it has to subtract column Y from column W and return a result. The other criteria, that it be in the month of September, 2009 and column B not have the word 'cancelled' in it, still apply.

I would really appreciate any help. All I get is an error no matter what I do.

Thanks!

Try this...

Use cells to hold the criteria:

• A1 = 9/1/2009
• B1 = 9/30/2009
• C1 = cancelled

=SUMPRODUCT(--(bookings!D1:D100>=A1),--(bookings!D1:D100<=B1),--(bookings!B1:B100<>C1),bookings!W1:W100-bookings!Y1:Y100)

I would avoid using entire columns as range references with the SUMPRODUCT function. Use smaller specific ranges.

 

[leopardhawk]

mvptomlinson, my apologies, I wasn't thinking all that well and I forgot to put in my Excel version. I am using Excel 2010. I uploaded a scrubbed version of my spreadsheet (no personal data) so you can see what I am trying to do. (http://www.mediafire.com/?s5xxw5qod08m220)

I appreciate your help and that of t.valko. I tried your suggestion and I am not getting the expected result ($54.08). I don't think I did a very good job of explaining what I am trying to do so I will try again.

I don't want the new formula to do anything at all with column V, just subtract column Y from column W IF the date in column X is in September 2009 AND IF the word 'cancelled' is NOT in column B. Hopefully, you can see this in the spreadsheet that I uploaded.

To further complicate the formula, I also need it to do the same thing in five other pairs of columns if that is even possible. What I mean is (and you can see it on the spreadsheet), I need the formula to not only look in column X for September 2009 but also in columns AD, AJ, AP, BH & BR and if it finds a date in September 2009, to do the same thing, subtract the columns on either side and return the TOTAL result.

When you look at the spreadsheet, you will see what I mean, there are dates all over the place depending on all sorts of criteria. I hope this makes sense.

Thanks again for all your help!

t. valko, I tried your suggestion and it did not work in my spreadsheet.

 

[T. Valko]

mvptomlinson, my apologies, I wasn't thinking all that well and I forgot to put in my Excel version. I am using Excel 2010. I uploaded a scrubbed version of my spreadsheet (no personal data) so you can see what I am trying to do. (http://www.mediafire.com/?s5xxw5qod08m220)

I appreciate your help and that of t.valko. I tried your suggestion and I am not getting the expected result ($54.08). I don't think I did a very good job of explaining what I am trying to do so I will try again.

I don't want the new formula to do anything at all with column V, just subtract column Y from column W IF the date in column X is in September 2009 AND IF the word 'cancelled' is NOT in column B. Hopefully, you can see this in the spreadsheet that I uploaded.

To further complicate the formula, I also need it to do the same thing in five other pairs of columns if that is even possible. What I mean is (and you can see it on the spreadsheet), I need the formula to not only look in column X for September 2009 but also in columns AD, AJ, AP, BH & BR and if it finds a date in September 2009, to do the same thing, subtract the columns on either side and return the TOTAL result.

When you look at the spreadsheet, you will see what I mean, there are dates all over the place depending on all sorts of criteria. I hope this makes sense.

Thanks again for all your help!

t. valko, I tried your suggestion and it did not work in my spreadsheet.

Sorry, but I won't open files with VBA code from unknown sources.

 

[leopardhawk]

T. Valko, thanks for your help. I have removed the VBA code and reposted the spreadsheet here: http://www.mediafire.com/?jenwm2o14el10wg

Thanks again.

 

[T. Valko]

T. Valko, thanks for your help. I have removed the VBA code and reposted the spreadsheet here: http://www.mediafire.com/?jenwm2o14el10wg

Thanks again.

Adapting my suggested formula to your file I get a result of 54.08.

I used cells to hold the date criteria:

• AC1 = 9/1/2009
• AD1 = 9/30/2009

=SUMPRODUCT(--(B7:B300<>"cancelled"),--(X7:X300>=AC1),--(X7:X300<=AD1),W7:W300-Y7:Y300)