Hi,
I'm trying to sum each months visa purchases. The data is in three columns: date,value and payment type.
I can't get this formula to work:-
{=SUM(IF(AND(Purchases!C6:C999>DATE(2009,12,28),Purchases!C6:C999<DATE(2010,1,29)),IF(Purchases!K6:K999="Visa",Purchases!H3:H999)))}
It's returning a value of 0 (and if that were true I'd be delighted!)
Thanks in advance.
Last edited by tghcogo; 02-22-2010 at 03:25 PM.
If you don't want to use a CSE formula then how about...
=SUMPRODUCT(--(C6:C10>DATE(2009,12,28)),--(C6:C10<DATE(2010,1,29)),--(K6:K10="Visa"),H6:H10)
Last edited by jeffreybrown; 02-21-2010 at 09:53 PM.
Thanks for the suggestion, but it won't work, I don't mind CSE, that's not an issue,
I still think Rocky has the right idea, though not the right formula:
=SUMPRODUCT(--(C6:C999>DATE(2009,12,28)), --(C6:C999<DATE(2010,1,29)), --(K6:K999="Visa"), H6:H999)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Try...
=SUM(IF(Purchases!C6:C999>DATE(2009,12,28),IF(Purchases!C6:C999<DATE(2010,1,29),IF(Purchases!K6:K999="Visa",H6:H10))))
Thanks for the quick response, unfortunately for some reason that still gives a zero value!
This relates to JBeaucaire's reply , you guys are too fast, I'll try Rocky1's now
Last edited by tghcogo; 02-21-2010 at 10:00 PM.
Post up a workbook with usable sample data showing the formula not working for you.
Click GO ADVANCED and use the paperclip icon to post up your workbook.
It works for me...
=SUM(IF(Purchases!C6:C999>DATE(2009,12,28),IF(Purchases!C6:C999<DATE(2010,1,29),IF(Purchases!K6:K999="Visa",Purchases!H6:H999))))
If you cut and paste the formula I posted look at the end...H6:H10 should be Purchases!H6:H999
Thamks, but taking out the AND doesn't seem to work either.
(wanders off scratching head)
Thanks Rocky, I have been cutting and pasting and CSE
@JBeaucaire I'll post a sample tomorrow, I'm going to bed now, IT's 2.00am here Waaaaah!
The formula is not CSE. SUMPRODUCT() is already an array function, so not typical to need to CSE on it.
Thanks, managed to get the sumproduct to work
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks