Hi,
I'm trying to sum a figure based on multiple criterias - I'm using sum and if array formulas but getting zero as an answer which is definitely incorrect.
=SUM(IF('Budget Detail'!$C$7:$C$3000='RBL '!$H52,IF('Budget Detail'!$B$7:$B$3000='RBL '!N2,IF('Budget Detail'!$BA$7:$BA$3000='RBL '!$O3,'Budget Detail'!$S$7:$S$3000,0),0)))
This is the formula I've got and I presee ctrl+shift+enter to get the curly brackets.....
I don't understand what is wrong with the formula as its very logical to me....can someone help? I'm desperate....its been bugging me for 2 days now...
Last edited by NBVC; 02-13-2012 at 11:16 AM.
Are you sure the matches are exact... no extra spaces, etc...
Also you can use:
=SUMIFS('Budget Detail'!$S$7:$S$3000,'Budget Detail'!$C$7:$C$3000,'RBL '!$H52,'Budget Detail'!$B$7:$B$3000,'RBL '!N2,'Budget Detail'!$BA$7:$BA$3000,'RBL '!$O3) with ENTER only in Excel 2010.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Thanks NBVC for your comment...it was a very stupid error I was making - column B was actually column BB - stupid mistake...
however, I like the sumifs function - I tried that earlier and wasn't working - having tried your solutions, its working now - so going to use that...
Additional question - in Column H - in the instance I only have 1 item - ie: RC45, the above formula works...
However, in some of the cells in Column H - I have several data - ie: RC45,RC50,RC406....and in different formats - ie: RC45,RC50..RC29
rather than creating separate columns for each individual RC code - what other method can I use in this instance???
So you want to get all rows that "contain" the string that is in H52?
Maybe:
=SUMIFS('Budget Detail'!$S$7:$S$3000,"*"&'Budget Detail'!$C$7:$C$3000,'RBL '!$H52&"*",'Budget Detail'!$B$7:$B$3000,'RBL '!N2,'Budget Detail'!$BA$7:$BA$3000,'RBL '!$O3)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Hiya.
That doesn't work for me.
Examples of the format of the data in the H columns below:
RL405,RL406,RL407
RL405,RL410,RL406,RL407
RB101..RB188,RC182..RC188,RC191..RC200,RC201..RC293
RL420..RL425
The formula below works if H19 contains only one code - ie: RC201
However, if it contains - 3 codes like - RL405,RL406,RL407
or a range of codes like - RB101..RB188,RC182..RC188,RC191..RC200,RC201..RC293
what do I do to make that formula work?
=SUMIFS('Budget Detail'!$S:$S,'Budget Detail'!$C:$C,'RBL '!$H19,'Budget Detail'!$BB:$BB,'RBL '!$O$2,'Budget Detail'!$BA:$BA,'RBL '!P$3)
Can you explain exactly what you want the formula to do? what is in 'RBL '!$H19, 'RBL '!$O$2, 'RBL '!P$3? And what is it looking for in 'Budget Detail' sheet?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
I'm trying to sum costs by:
1) Cost Centre - there's hundreds of them - these are in H column - and per above - in the format of RL405,RL406,RL499..RL600 etc
2) Location - ie: central/regional/local - this information is in cells O2, V2 and AC2 respectively
3) type of cost - ie: gross salary/pensions etc - this information is in cells - P3,Q3 respectively (for central), W3,X3 respectively (for regional)
budget detail sheet is where the data is......
therefore, total costs in Column S of budget detail
Cost Centres - in column C of budget detail (Column H in main spreadsheet)
Location - in column BB of budget detail (Cell N2 in main spreadsheet - for central)
Gross Salary/pensions etc in column BA of budget detail (Cell O3 in main spreadsheet - for central)
RBL H 19 - is the cost centre
Maybe I should have asked too.. but could you post a small sample workbook that allows me to better understand.
Right now I am not sure why my formula with wildcards doesn't work?
=SUMIFS('Budget Detail'!$S$7:$S$3000,"*"&'Budget Detail'!$C$7:$C$3000,'RBL '!$H52&"*",'Budget Detail'!$B$7:$B$3000,'RBL '!N2,'Budget Detail'!$BA$7:$BA$3000,'RBL '!$O3)
It assumes that column H of 'RBL ' sheet has say, RL405 and looks for that code amongst all the cells in 'Budget Detail'!$C$7:$C$3000 no matter where in the cell the code appears amongst other codes...
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Yes, your last sentence is correct....but what I'm trying to do is say for example, Column H has RL405,RL406,RL407 - to include those codes and look them up in the budget detail sheet as well - and the wildcards wasn't picking it up.
I will probably to separate them out into separate columns and than do a sumifs formula - as there is no consistency in the data in Column H.
However, I do still need help with where the data in column H is a range - ie: RL450..RL599,RL600,RL650..RL699
How can I expand the range to give me the codes between the ranges? therefore, RL451,RL452 etc?
Hi,
Can someone tell me what is wrong with this formula please? It was working when I had one cell defined where its now $P:$P...however, there are several items that need to be looked up in that column and making it $P:$P - has made this formula redundant. Is there another way of achieving what I'm trying to do?
Really would appreciate help on this.
=SUMIFS('Budget Detail'!$S:$S,'Budget Detail'!$C:$C,'RBL (3)'!$P:$P,'Budget Detail'!$BB:$BB,'RBL '!$O$2,'Budget Detail'!$BA:$BA,'RBL '!P$3)
Can you give a better description of exactly what you mean by:
Are you still looking in column P? If yes, for multiple items, then what are those.It was working when I had one cell defined where its now $P:$P...however, there are several items that need to be looked up in that column and making it $P:$P
Like I said before, a sample workbook would be great!
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Thanks for your quick response - In column P - yes, I'm still looking in Column P - the data are the cost centres - RL405/RL406 etc...
How can I send you the worksheet without displaying it to all here?
Change the worksheet up so that all confidential info is no longer available.. you can make the sample a lot smaller too, as long as the just of it is still there... and post it in the forum.
To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.
On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.
You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.
To upload a file from your computer, click the 'Browse' button and locate the file.
To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.
Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Sample file attached above
So, trying to understand now....
Are you looking to see if any of the cells in column C of Budget Detail contains any of the items listed in column P of RBL (3)?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks