Hello Everybody,
I want to calculate hours for every truck in each day.A forum member helped me using sumproduct formula but it is very heavy in my original file.
Required:
I want to use "sumifs" rather than "sumproduct"
See the attachment
Waiting your help
Last edited by leprince2007; 01-31-2017 at 03:23 AM.
Try this...
AA3=SUMIFS($V$4:$V$26,$A$4:$A$26,$Z3,$B$4:$B$26,AA$2)
Also, use this in V4
=SUMIFS(E4:T4,$E$3:$T$3,"hours")
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Please I want the function to depend on itself.Originally Posted by FDibbins
Please download the attachment again I edited it.
AC15=SUMIFS(INDEX($E$4:$T$100,,MATCH(AC$13,$E$2:$T$2,0)),$A$4:$A$100,$AA14,$U$4:$U$100,$AB14)
Please Login or Register to view this content.
Try this and copy across
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
In AB4
=SUMIFS($W:$W,$B:$B,AB$3,$A:$A,$AA4)
in AC14
=SUMIFS($E:$E,$A:$A,$AA14,$U:$U,$AB14)
Change E:E to G:G, I,K, .... etc for each material
OR use NflSales generic formula.
Last edited by JohnTopley; 01-31-2017 at 04:04 AM.
The formula in AC15 works great.
But the formula in AB4,I want it to depend on itself.
waiting your feedback
You have answers already.
Last edited by JohnTopley; 01-31-2017 at 05:00 AM.
The formula in AB4,I want it to depend on itself.I mean Not to depend on "totals" column
waiting your feedback
Last edited by leprince2007; 01-31-2017 at 05:08 AM.
Why when you already know the total??
I doubt if there is a more efficient way than SUMPRODUCT to accomplish this.
=SUMPRODUCT(($E$4:$T$100)*($E$3:$T$3="Hours")*($A$4:$A$100=$AA4)*($B$4:$B$100=AB$3))
Just try it
I have and it works.
Filtering for 22222 shows 8 hours for 20 NOV ( 1 and 7 ) as there are 2 entries
Phocam's formula using match will only select the first (value 1)
who is correct?
Last edited by JohnTopley; 01-31-2017 at 06:55 AM.
Removed by Phuocam.
Last edited by Phuocam; 01-31-2017 at 06:45 AM.
Up....up.....up
So EXPLAIN what is wrong with SUMPRODUCT. what results are wrong?
and please answer why not SUMIF .....
I am (almost) certain it cannot be done with SUMIFS as there is no way to select the hours column: hence the use (flexibility) of SUMPRODUCTWhy when you already know the total??
Last edited by JohnTopley; 01-31-2017 at 03:22 PM.
If you were prepared to change your layout so that you had 7 columns, i.e. your first four then columns for; Material Type, Units, & Hours you'd open up the powerful world of the Pivot Table and in the process give you a lot more flexibility for subsequent analysis and summarisation. You'd also avoid needing any functions or indeed VBA.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Using Richard's format see attached:
in M5
=SUMIFS($G:$G,$A:$A,$L4,$B:$B,M$3)
in N14
=SUMIFS($F:$F,$A:$A,$L14,$H:$H,$M14,$E:$E,N$13)
What is the problem with using the TOTAL column? It is there, why not use it?
Sumproduct gives the right answer.But it makes my file heavy in calculation .So i want to try another formula.
Can you use any other formula that is faster in cakculation?
Note: I use it in large data,and I use it more than 100 times
No: SUMIF is fastest BUT as you don't want to use the TOTAL .......
OR
you re-organise your file as per Richard's suggestion and then you can use SUMIFS (or PIVOT tables ... not my area of expertise!).
How large is your data set (number of rows) ?
-YMy data is more than 1000 rows and the formula itself I use it a lot.
Can you use any other formula??
Last edited by leprince2007; 01-31-2017 at 05:08 PM.
As I have explained you only have 2 realistic options: SUMIF(s) or SUMPRODUCT.
SUMPRODUCT on the formula I gave will complete in no time: so I suggest there are other problems.
With SUMPRODUCT do not use full column ranges (A:A): use A2:A1000
Last edited by JohnTopley; 01-31-2017 at 05:17 PM.
Ok thank you very much
I created a data set with 900 + rows and changing data does not seem to impact the SUMPRODUCT formula. It updates instantly.
You also have not said (that I can see) why you dont want to use the TOTAL column?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks