Using SUMIF I'm trying to get cell B13 to pull month from cell A13 to use as criteria to pull from Income sheet, however I havent been able to get it to work.
=SUMIF(Income!C:C,"="&MONTH(A13),Income!D:D)
including spread sheet.
Values are:
Currency: Japanese Yen
Date: yyyy/mm/dd
would like cell A13 to be just month and year.
version excel 2010
Thank you for any assistance,
ToriOtokoJapan Budget.xlsx
Hello & Welcome to the Forum,
In B13 copied down >> =SUMPRODUCT(--(TEXT($A13,"mmyyyy")=TEXT(Income!$C$2:$C$25,"mmyyyy")),Income!$D$2:$D$25)
Change A13 to 8/1/2013 with a custom format of >> mmmm
HTH
Regards, Jeff
OK, I think I missed something here. Copied and pasted =SUMPRODUCT(--(TEXT($A13,"mmyyyy")=TEXT(Income!$C$2:$C$25,"mmyyyy")),Income!$D$2:$D$25)
and I got TRUE in field.
Also this will be a fluid document and want to select the column as a whole instead of a specified range of cells within the Income sheet.
Thanks again for the quick answers, specially considering time of night where you are.
B13=SUMIFS(Income!$D$2:$D$10000,Income!$C$2:$C$10000,">="&A13,Income!$C$2:$C$10000,"<="&EOMONTH(A13,0))
I would not recommend to use thw whole column range as it will slow down calculation but if you insist you can use C:C and D:D.
As jeffrey pointed:
Change A13 to 8/1/2013 with a custom format of >> mmmm
Last edited by RobertMika; 09-08-2013 at 05:04 AM.
If you arehappy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
"I would not recommend to use thw whole column range as it will slow down calculation but if you insist you can use C:C and D:D."
Generally, yes, though as I understand it, one of the advantages of the SUMIF(S), COUNTIF(S), etc. family of functions is that they calculate the UsedRange only; hence no loss in performance is typically associated with referencing entire columns.
Regards
You are right XOR LX.
True for SUMIF(S) but not for COUNTIFS.
I should have rarher say:Not feasibe and I woud I would rather not use.
Ah, interesting. I didn't realise that COUNTIFS was different in this sense. Wonder why?
Cheers
That worked great! Thanks for the timely replyOriginally Posted by XOR LX
Look here:
COUNTIFS/SUMIFS
Robert, that's some really fascinating stuff.
Thanks a lot.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks