LinkBack URL
About LinkBacks
Greetings,
How may I make the following formula look for the word "Online" in columns C, D, E, or F as well?
=SUMPRODUCT(--(C:C="Online"),--(A:A=>DATE(2008,1,1)),--(A:A<=DATE(2008,12,31)),B:B)
Greetings,
How may I make the following formula look for the word "Online" in columns C, D, E, or F as well?
=SUMPRODUCT(--(C:C="Online"),--(A:A=>DATE(2008,1,1)),--(A:A<=DATE(2008,12,31)),B:B)
Try:
=SUMPRODUCT((C:F="Online")*(A:A>=DATE(2008,1,1))*(A:A<=DATE(2008,12,31))*B:B)
although probably very slow.. You should use defined ranges instead of full ranges...
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
I think, in your situation, you could use something like this:
Using fewer functions should reduce the calculation overhead and shorten the
Please Login or Register to view this content.
recalc time.
Note: that formula only works if the same row will NOT contain "online" more than once.
Is that something you can work with?
Ron, I don't have a stop watch or anything.. but that didn't really seem that noticeably quicker when I plugged it in my sample sheet...
... and as Ron mentioned, if ONLINE appears more than once, then it will sum column B that many more times at those rows of duplication....
I did say "should reduce..."Originally Posted by NBVC
Sometimes it's a combination of small, seemingly insignificant, efficiencies that cause a noticeable reduction in recalc time.
Thanks folks, but both formulas are resulting in a #VALUE! error
Probably you have a text title in column B... you can't have that with this formula...
You need to eliminate the titles or used defined ranges that start at row 2.
Folks,
Thanks again, I haven't gotten it to work right so far, but before we proceed I want to verify what you had said (about if the word "online" was in the row, more than once) in an example:
A B C
$50 Online Online
$10 Online
$20 Online
I am hoping that the count would be 3 and the total would be $80, but from what people were saying about "online" appearing the row more than once it could be count 4 and total $130. Please confirm either way.
Thanks a lot!
Bryce
It is the latter... 4 and $130
One solution would be to add a helper column that identifies if there is at least one "Online" in the row and then use that column in your sumproduct...
so if in column A you add formula:
=ISNUMBER(MATCH("online",C1:F1,0)) and copy down...
then =SUMPRODUCT((A:A=TRUE)*B:B)
will find the TRUE's in column A and return 1 for each TRUE found and multiply that by corresponding cell in column B.. then sum them all up for a grand total.
I like the helper column idea a lot, but I can make it do more for me like, instead of resulting in TRUE, result in Online?
If the answer is yes, can you give me a formula that can do that for several categories, like Direct Mail, etc???
So the results would be:
A B C D
Online $50 Online Online
Direct Mail $180 Direct Mail
Visit $15 Visit
Newsletter $20 Newsletter
Thanks so so much!
Bryce
Try:
=INDEX(C1:F1,MATCH(TRUE,INDEX(C1:F1<>"",0),0))
copied down.
This is great. One more addition (if possible)
If column D = "Multiple" then display text from column E
Thanks!
Is that to be incorporated in the previous formula so that it overrides the previous formula result?
Yes
Because most of the columns are repeated,
C D
Newsletter Newsletter
Some are empty in D
Newsletter
And some are
Multiple Newsletter
I want them to be newsletter too.
I'm going to say:
=IF(D1="Multiple",E1,INDEX(C1:F1,MATCH(TRUE,INDEX(C1:F1<>"",0),0)))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks