Array Formula ?

[Javi]

I have 3 columns I would like to do an array multiplication formula with an if statement. I have tryed SUMIF and SUMPRODUCT with no luck.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Column A represents Quantity A5:A5000
<o> </o>
Column B represents Criteria B5:B5000
<o> </o>
Column C represents Price C5:C5000
<o> </o>
I would like the formula to multiply Column A by Column C provided Column B equals (0) zero and return a total of the array.

 

[LxQ]

this worked for me:
=SUMPRODUCT(--(A5:A5000),--(B5:B5000=0),--(C5:C5000))

 

[Javi]

Worked Great Thanks!!!

 

[mahhogany]

Hi

I have a somewhat similar issue with arrays and countIfs formulas

I would like to count the values in an array only if they match values in another array. To make things even more difficult I would like to nest this formula in within with other criteria using the countifs function. It is falling over because it doesn't seem to like array to array mathing. The same thing is happening when I use the sumproduct function.

Here is the my formula below.

=COUNTIFS('Tracker'!P:P,Data!B2,'Tracker'!w:w,Data!A16:A28,'Tracker'!I:I,"Let")

Returns 0

The bolded bit is the problem because when I remove the A16:A28 bit and replace it with just A16 it works but it is not suitable as I need it to count everthing that matches the criteria in A16:A28.

 

[barry houdini]

You can do it similar to the way you are trying with COUNTIFS.....but that formula returns an "array" of values (one for each element of Data!A16:A28) so you need to wrap a SUMPRODUCT function around the COUNTIFS to sum those, i.e.

=SUMPRODUCT(COUNTIFS('Tracker'!P:P,Data!B2,'Tracker'!w:w,Data!A16:A28,'Tracker'!I:I,"Let"))

Note that if there are any duplicates in Data!A16:A28 then that means you'll be counting more than once.....

 

[mahhogany]

You can do it similar to the way you are trying with COUNTIFS.....but that formula returns an "array" of values (one for each element of Data!A16:A28) so you need to wrap a SUMPRODUCT function around the COUNTIFS to sum those, i.e.

=SUMPRODUCT(COUNTIFS('Tracker'!P:P,Data!B2,'Tracker'!w:w,Data!A16:A28,'Tracker'!I:I,"Let"))

Note that if there are any duplicates in Data!A16:A28 then that means you'll be counting more than once.....

WOW! It worked. And here I was thinking that this was impossible to do. Many thanks and God bless!

P.S..There are no duplicates in the A16:A28 so it worked perfectly.