Using SUMIFS with search criteria that use cell references

M

Mr Bonza

New Member
Joined
Apr 3, 2007
Messages
19
Hi,

I am using the SUMIFS function in the following formula and it works with no problems:

=SUMIFS(C1:C10, A1:A10,">=1/5/8",A1:A10,"<=31/5/8",B1:B10,"VISA")

where column A is a list of random dates for May
where column B is a list of payment methods that includes VISA
where column C is a list of amounts paid

As data is added to the spreadsheet the range of dates will span the whole year, so I want to be able to change the date criteria by referring to independent cell references for the dates criteria. In this way I can easily sum the amounts paid by VISA in any given month.

I have successfully used SUMPRODUCT achieve the same result as I'm getting with SUMIFS, but still have the same issues of not being able to change the date criteria by reference to cell values. I think this method falls down because the criteria must be "TEXT". I suspect this may be the same problem with SUMIFS.

I have tried the following variations without success:

=SUMIFS(C1:C10, A1:A10,">=D1",A1:A10,"<=D2",B1:B10,"VISA") - cell reference

=SUMIFS(C1:C10, A1:A10,>=D1,A1:A10,<=D2,B1:B10,"VISA") - cell reference no quotes

=SUMIFS(C1:C10, A1:A10,">=(D1)",A1:A10,"<=(D2)",B1:B10,"VISA") - brackets around cell reference and quotes

Sorry to be so long winded. I will appreciate any help that may be offered. Sean
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
Hi Mr Bonza:

If I understood you correctly, following would be one way ...

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABCD
1DateModeAmount5/15/2008
25/23/2008check238
35/19/2008MC3*
45/22/2008VISA4*
55/15/2008MC5*
65/12/2008VISA6*
75/19/2008MC7*
85/22/2008check8*
95/15/2008MC9*
105/12/2008VISA10*
Sheet8


</body></html>
 
Upvote 0
Thanks. Your formula doesn't account for the range that I want to search within. I'm trying to sum all the VISA purchases between 1/5/8 and the 31/5/8. Your SUMIF part of the formula is only looking at one date.

Having said all that, the way you have used the &D1 might work within the SUMIFS formula.

I'll have to try it when I get home tonight as I only have Excel 2003 at work. I'll get back to you after I have tried it. Sean
 
Upvote 0
Follow up to my earlier reply. On closer inspection I realise I missed exactly what you've suggested. Unfortunately all the purchase dates after May 31 will be picked up eg June etc, as they are in the same column, so I need a way to stop the formula at May 31.
 
Upvote 0
Mr Bonza said:
Thanks. Your formula doesn't account for the range that I want to search within. I'm trying to sum all the VISA purchases between 1/5/8 and the 31/5/8. Your SUMIF part of the formula is only looking at one date.

Having said all that, the way you have used the &D1 might work within the SUMIFS formula.

I'll have to try it when I get home tonight as I only have Excel 2003 at work. I'll get back to you after I have tried it. Sean
Click to expand...

The SUMIFS function is not available in EXCEL 2000 (that I am using) ... so following is one way ...

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABCDE
1DateModeAmount1-May-200831-May-2008
223-May-2008check214*
319-May-2008MC3**
422-May-2008VISA4**
515-May-2008MC5**
612-Jun-2008VISA6**
719-May-2008MC7**
822-May-2008check8**
915-May-2008MC9**
1012-May-2008VISA10**
Sheet8 (2)


</body></html>
 
Upvote 0
hi Sean, (a bit off-topic question)

you say you are working with a formula called *SUMIFS()* ? Where did you find this function ? I mean is this formula available in Excel 2007 ? I use Excel 2007 but it is installed on my home computer. I could not find SUMIFS() formula in Excel 2002.

Is it a UDF or something ? Which version of Excel do you use on your computer ?
 
Upvote 0
Try...

=SUMIFS(C1:C10, A1:A10,">="&D1,A1:A10,"<="&D2,B1:B10,"VISA")

Mr Bonza said:
Hi,

I am using the SUMIFS function in the following formula and it works with no problems:

=SUMIFS(C1:C10, A1:A10,">=1/5/8",A1:A10,"<=31/5/8",B1:B10,"VISA")

where column A is a list of random dates for May
where column B is a list of payment methods that includes VISA
where column C is a list of amounts paid

As data is added to the spreadsheet the range of dates will span the whole year, so I want to be able to change the date criteria by referring to independent cell references for the dates criteria. In this way I can easily sum the amounts paid by VISA in any given month.

I have successfully used SUMPRODUCT achieve the same result as I'm getting with SUMIFS, but still have the same issues of not being able to change the date criteria by reference to cell values. I think this method falls down because the criteria must be "TEXT". I suspect this may be the same problem with SUMIFS.

I have tried the following variations without success:

=SUMIFS(C1:C10, A1:A10,">=D1",A1:A10,"<=D2",B1:B10,"VISA") - cell reference

=SUMIFS(C1:C10, A1:A10,>=D1,A1:A10,<=D2,B1:B10,"VISA") - cell reference no quotes

=SUMIFS(C1:C10, A1:A10,">=(D1)",A1:A10,"<=(D2)",B1:B10,"VISA") - brackets around cell reference and quotes

Sorry to be so long winded. I will appreciate any help that may be offered. Sean
Click to expand...
 
Upvote 0
Hi, Thank you all for your assistance - the problem is solved. I don't know how to reply to you individually, so you have to get a group thank you.

Thanks to Yogi - your last solution worked
Thanks to Aladin - your solution also worked and I got a clue as to how it might work in Yogi's first reply using the &

And Stormseed, I'm using 2007 - once you get the hang of the ribbon it's brilliant and the improved/increased functions are a bonus.

Thanks again, Sean
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Using one cell (text separated by comma) as an array in SUMIFS formula
Replies
1
Views
250
danhendo888
D
S
  • Question Question
SUMIF and Search
Replies
2
Views
409
Super Mario
S
R
  • Question Question
Retrieve cells with highest sumif values
Replies
5
Views
136
KOKOSEK
KOKOSEK
T
  • Question Question
Lookup to return nth value
Replies
5
Views
2K
tinderbox22
T
B
  • Question Question
Sum child cells and allow reference to higher cell within Table
Replies
7
Views
160
babyjwhale
B

Forum statistics

Threads
1,224,883
Messages
6,181,550
Members
453,052
Latest member
ezzat

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top