Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM, IF, OR, AND array
I am seeking a formula for the following:
On the current worksheet, if $B2 = Chart1!$L$2:$L$7500 OR Chart1!$N$2:$N$7500 AND If $A2 = Chart1!$C$2:$C$7500 AND If $C2 = Chart1!$B$2:$B$7500 then I want to add all corresponding numbers in Chart1!$AV$2:$AV$7500. thanks in advance, Bob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM, IF, OR, AND array
Try this:
=SUMPRODUCT(--(Chart1!$B$2:$B$7500=$C2),--(Chart1!$C$2:$C$7500=$A2),--((Chart1!$L$2:$L$7500=$B2)+(Chart1!$N$2:$N$7500=$B 2)0),Chart1!$AV$2:$AV$7500) -- Biff Microsoft Excel MVP "bob" wrote in message ... I am seeking a formula for the following: On the current worksheet, if $B2 = Chart1!$L$2:$L$7500 OR Chart1!$N$2:$N$7500 AND If $A2 = Chart1!$C$2:$C$7500 AND If $C2 = Chart1!$B$2:$B$7500 then I want to add all corresponding numbers in Chart1!$AV$2:$AV$7500. thanks in advance, Bob |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM, IF, OR, AND array
It is not clear on what you want to do, for instance
what is in Chart1!$L$2:$L$7500 or Chart1!$N$2:$N$7500 that you want to look and based on that add to the total? post a sample and your the result you are seeking for better undertanding -- HTH Pls provide your feedback by clicking the YES button below if this posting is helpful This will help others to search the results in the archive better cheers, francis "bob" wrote in message ... I am seeking a formula for the following: On the current worksheet, if $B2 = Chart1!$L$2:$L$7500 OR Chart1!$N$2:$N$7500 AND If $A2 = Chart1!$C$2:$C$7500 AND If $C2 = Chart1!$B$2:$B$7500 then I want to add all corresponding numbers in Chart1!$AV$2:$AV$7500. thanks in advance, Bob |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM, IF, OR, AND array
this seems to work...thank you very much. can you please tell me the function
of the two dashes (--). thanks. "T. Valko" wrote: Try this: =SUMPRODUCT(--(Chart1!$B$2:$B$7500=$C2),--(Chart1!$C$2:$C$7500=$A2),--((Chart1!$L$2:$L$7500=$B2)+(Chart1!$N$2:$N$7500=$B 2)0),Chart1!$AV$2:$AV$7500) -- Biff Microsoft Excel MVP "bob" wrote in message ... I am seeking a formula for the following: On the current worksheet, if $B2 = Chart1!$L$2:$L$7500 OR Chart1!$N$2:$N$7500 AND If $A2 = Chart1!$C$2:$C$7500 AND If $C2 = Chart1!$B$2:$B$7500 then I want to add all corresponding numbers in Chart1!$AV$2:$AV$7500. thanks in advance, Bob |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM, IF, OR, AND array
Double negation coerces Boolean FALSE/TRUE to 0/1
For more details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "bob" wrote in message ... this seems to work...thank you very much. can you please tell me the function of the two dashes (--). thanks. "T. Valko" wrote: Try this: =SUMPRODUCT(--(Chart1!$B$2:$B$7500=$C2),--(Chart1!$C$2:$C$7500=$A2),--((Chart1!$L$2:$L$7500=$B2)+(Chart1!$N$2:$N$7500=$B 2)0),Chart1!$AV$2:$AV$7500) -- Biff Microsoft Excel MVP "bob" wrote in message ... I am seeking a formula for the following: On the current worksheet, if $B2 = Chart1!$L$2:$L$7500 OR Chart1!$N$2:$N$7500 AND If $A2 = Chart1!$C$2:$C$7500 AND If $C2 = Chart1!$B$2:$B$7500 then I want to add all corresponding numbers in Chart1!$AV$2:$AV$7500. thanks in advance, Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent cell/array references from changing when altering/moving thecell/array | Excel Discussion (Misc queries) | |||
Populate an array that is a subset of a larger array? | Excel Worksheet Functions | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
combining cells and array from different sheets into an array to pass to IRR() | Excel Discussion (Misc queries) | |||
Goal Seek On Members of an Array within Array | Excel Worksheet Functions |