Simple SUM IF Formula is not working...
=SUM(SUMIFS('Project Tracking'!O:AS,'Project Tracking'!B:B,A3))
Or
Since I created defined names for cell ranges
=SUMIF(January,Asset,A3)
Can some one please tell me what I am missing??? Why is this not working?
Thank you in advance
I would approach it differently, though merged cells can look good on reports they often cause problems. (Personally I'm no fan of defined name ranges because for me they can make it difficult to debug formulas so I didn't use any.) Now, I used a sumproduct and you can either hide the dates I put in row 3 or change them to a font color that matches the cell color so they are not visible.
Here is the sumproduct...
=SUMPRODUCT((MONTH('Project Tracking'!$O$3:$NO$3)='Revenue Tracking'!B$1)*('Project Tracking'!$B$8:$B$16='Revenue Tracking'!$A3),'Project Tracking'!$O$8:$NO$16)
I am attaching your sheet with the sumproduct in the revenue tracking tab so you can see what the formula refers to.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Just a patch to Sam's formula (if I may), so as to dismiss helper rows in both tabs.
In B3, copied across and down:
Formula:
Please Login or Register to view this content.
@Sugarprincess21
Administrative Note:
Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Post deleted.
Last edited by kvsrinivasamurthy; 01-09-2022 at 07:42 AM.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
SUMIF ranges should be the same size otherwise you will get erroneous results.
https://support.microsoft.com/en-us/...2-1697a653039b
I would use the formula in post #3 BUT do not use full column ranges with SUMPRODUCT: set upper range to likely maximum
=SUMPRODUCT((MONTH('Project Tracking'!$O$5:$NO$5)=MONTH(DATE(2022,COLUMN(A$1),1)))*('Project Tracking'!$B$8:$B$1000='Revenue Tracking'!$A3),'Project Tracking'!$O$8:$NO$1000)
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Sorry to bug you for more help but I tried transferring your fix to my working copy along with combining the other suggestions as I do need the cells to be somewhat infinite as more project tracking gets added and its back to not working
I have attached another updated example, I had to delete a lot of it as the file was too large but I think there is enough there to figure out what is going on.
Last edited by Sugarprincess21; 01-09-2022 at 02:58 PM. Reason: attachment was too large
Change the months in row 3 to GENERAL/NUMBER not TEXT as Excel is comparing the numeric month from the date with your TEXT values: mismatch!
Or use this new version, which does not require values in row 3:
Formula:Please Login or Register to view this content.
So which version of Excel do you have? Your profile still says 10 - that is your Windows OS, not your Office version.
Microsoft 365 for windows
Thanks Estevaoba,
It's working perfectly so far, I am really unfamiliar with using SUMPRODUCT and MONTH functions... What is "=COLUMNS$A$1:A$" supposed to be referring to though just for my knowledge and understanding.
You're welcome. Glad it worked.
=COLUMNS($A$1:A$1) equals 1 and turns into 2, then 3... as you copy across, so it is a resource you can use when you need to refer to a sequence, like month numbers.
Thank you for the feedback, for the reputation added and for marking the thread solved.
And don't forget to thank all the others that have helped you by adding to their reputation, especially Sam Capricci, who started out this formula.
Best wishes for the new year!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks