And I thought I finally had this mastered with the help I have received here. I have checked everything I can think of as to why the attached is not returning the correct values and it's probably something really simple but I can't find it. All help appreciated.
Hello
You probably need this formula---
Use this formula
=SUMIF(Ferris!$E$6:$E$1100,A7,Ferris!$G$6:$G$1100)
Hope it helps
If you are satisfied just click on ADD REPUTATION below.....
Regrds
Sourabh Gupta
The sum product formula is used to multiply all values in two or more columns and add them up
like
Column A B C D
4 5 6 7
8 5 3 2
=sumproduct(A:A,B:B,C:C,D:D)
Sum product formula will do this
Column A B C D
4 * 5 * 6 * 7 = 840
8 * 5 * 3 * 2 = 240
______
1080
Moreover we cant enter a condition for the same
While using a sumif formula we can enter the range and our criteria and our sum range too....
If we have more then 1 condition then we can use sumifs
Hope it helps
Regards
Sourabh Gupta
Since you're using Excel 2003, SUMIF isn't an option.
Try this variant of what you were using:
=IF($A7="","",SUMPRODUCT((Ferris!$E$6:$E$1100=$A7)*Ferris!G$6:G$1100))
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
This works Glenn although one cell is returning #VALUE - I have attached the file as sample1 with the cell highlighted. I also checked the column entries to ensure they are all numeric. I am also attaching a similar spreadsheet (Sample2) I did previously which is essentially a variation on the same sort of thing and works without needing the =IF portion of the show at all. I am unclear as to why one needs it and the other doesn't.
Found that cell T38 sheet Ferris has a space character in it. Delete it and get correct result.
Quang PT
First off. Go to cell T38 on "Tours". There's something in the cell. delete it. Then that bit is fine.
in your original example, there are no data after row 788. There is no invoice number. SUMPRODUCT thinks it has a match when it gets down to row 14 on the summary and makes mistakes. the If bit is an error trapping phrase. Much easier in later versions of Excel where sumif and sumifs can be used.
You got there, then!! I didn't refresh before posting.
Just input =SUMIF(Ferris!$E$6:$E$1100,A7,Ferris!$G$6:$G$1100) as per sourabhg98's suggestion and that eliminates the #VALUE return in that one cell -which also makes me curious as to why one works in all but one cell and the other works in all of them.
I'm talking cr@p. It's sumIFS that isn't available in Excel 2003. SUMIF is.
The Excel version is 2010..????
That is not what it says in your profile (below your name in every post)...
SUMIF is preferable for this scenario, as others have suggested, but SUMPRODUCt will work if you use the correct syntax. In your original file you used this version:
=SUMPRODUCT(Ferris!G$6:G$1100,(Ferris!$E$6:$E$1100)=$A7)
That doesn't work because the second part just returns TRUE/FALSE values which SUMPRODUCT ignores - you need to convert those values to 1/0 values....and Glenn's version does that by multiplying the two arrays, i.e.
=SUMPRODUCT((Ferris!$E$6:$E$1100=$A7)*Ferris!G$6:G$1100)
The problem you get with that is that you get #VALUE! error if there's any text in the sum range, so the solution is to use this syntax:
=SUMPRODUCT(Ferris!G$6:G$1100,(Ferris!$E$6:$E$1100=$A7)+0)
If there's text in the sum range that will be ignored
Audere est facere
I have updated my profile to reflect the correct version of Excel - sorry for causing any inconvenience.
Is there a quick way to determine where the text values are? I scanned the the column but obviously missed it and definitely don't want the forumla to ignore what should be a numeric value.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks