Using the SUMIFS and SUMPRODUCT functions and getting arrays to correctly sum
I am trying to get this function to add only the specified rows of 6414,6415,6416,6425 but it is not summing and giving me a value of 0.
=SUMPRODUCT(SUMIF(A:A,"*6416*",E:E),SUMIF(A:A,"*6414*",E:E),SUMIF(A:A,"*6415*",E:E),SUMIF(A:A,"*6425*",E:E))
When I eliminate part of the function to only include a portion that I know gets results it correctly gives me the right amount but I need them all to be included.
=SUMPRODUCT(SUMIF(A:A,"*6416*",E:E))
Any thoughts?
you get better help if you add a small excel file, without confidential information.
please also add manualy the expected result in your file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Should I insert an image of the excel file or the file itself, something that can be edited within excel?
The sumproduct would be MULTIPLYING each result of SUMIF, not ADDING.
So it would do
SUMIF(A:A,"*6416*",E:E)*SUMIF(A:A,"*6414*",E:E)*SUMIF(A:A,"*6415*",E:E)*SUMIF(A:A,"*6425*",E:E)
To ADD them, you would just change SUMPRODUCT to SUM in your original formula.
Or
=SUMIF(A:A,"*6416*",E:E)+SUMIF(A:A,"*6414*",E:E)+SUMIF(A:A,"*6415*",E:E)+SUMIF(A:A,"*6425*",E:E)
Here's a shortcut for that.
=SUM(SUMIF(A:A,{"*6416*","*6414*,"*6415*","*6425*"},E:E))
Last edited by Jonmo1; 06-24-2015 at 01:06 PM.
Thank you, thank you!!! That makes sense, I didn't catch that! This forum is the best
You're welcome.
I'm not clear what the use of wild cards is achieving....
=SUM(SUMIF(A:A,{6416,6414,6415,6425},E:E))
if these four numbers are in column A, the corresponding values in column E will be summed.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
=SUM(SUMIF(A:A,"*6416*",H:J),SUMIF(A:A,"*6414*",H:J),SUMIF(A:A,"*6415*",H:J),SUMIF(A:A,"*6425*",H:J),SUMIF(A:A,"*158*",H:J),SUMIF(A:A,"*6412*",H:J))
This formula now is yielding 0 is it because of the H:J?
Column A must contain text strings like ABC6416DEF or something..Originally Posted by Glenn Kennedy
You're right it wouldn't work on numeric values in A.
Last edited by Jonmo1; 06-24-2015 at 01:19 PM.
Yes
Both the Criteria Range and the Sum Range must be same dimensions.
Well how do I include columns H:J because I need to be pulling from these three columns only to A:A
Is there going to be any more requirements added?
=SUM(SUMIF(A:A,{"*6416*","*6414*","*6415*","*6425*"},H:H),SUMIF(A:A,{"*6416*","*6414*","*6415*","*6425*"},I:I),SUM(SUMIF(A:A,{"*6416*","*6414*","*6415*","*6425*"},J:J)))
Any reason you can't just include 3 formulas?
Thank you btw Jonmo1 - that was a neat trick - didn't know you could include the wildcard in that way
Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks