SUMPRODUCT & ARRAYS - multiple criteria in the same row 1

[Skolastix]

I have used some to the threads to FINALLY come up with this formula:

=SUMPRODUCT((($B$4:$J$4=$A$23)+($B$4:$J$4=$A$24)+($B$4:$J$4=$A$25))*($B$9:$J$9))

...which allows me to add the values in different columns depending on the criterias addedd in Cells A23, A24 and A25.

Now my question: i am looking to simplify it using an array. I have encountered an example that uses one as per below:

----------------

Example 8: Count the instances of more than one value in a given range. This example is counting how many Fords and Chryslers are in the range A1:A10.

Solution: This can be solved by using OR as described in the previous example, but on a single range, that is
=SUMPRODUCT((A1:A10="Ford")+(A1:A10="Chrysler"))

But in this in stance, as we are looking for two values in a single range, it is better to test against an array of values, or
=SUMPRODUCT(--(A1:A10={"Ford","Chrysler"}))

---------------

...but it seems as though this example only works if the array is in the same column. Example: if the original equation was
=SUMPRODUCT((A4:J4="Ford")+(A4:J4="Chrysler"))

the formula:
=SUMPRODUCT(--(A4:J4={"Ford","Chrysler"}))
doesn't seem to work...

How can I use the example to simplify the SUMPRODUCT formula if the results are all in the same row (ex: row 4)

Any help would be appreciated. Thanks in advance

Stais

 

[Zathras]

This is what database formulas were invented for. DCOUNT provides the easiest solution for this kind of situation.

But, if your only tool is a hammer, then everything looks like a nail.

 

[bluedragon2]

Zathras is correct with the DCOUNT, or the SUMPRODUCT could look like the following:

Column A is cost, Column B is type:

=SUMPRODUCT((A1:A100)*(B1:B100="Ford"))+SUMPRODUCT((A1:A100)*(B1:B100="Chrysler"))

for the total cost of all Ford's and Chrysler's



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Skolastix]

Thanks! I tried those solutions< but my goal is to make this formula as short as possible so nested formulas is not an option.

Well I came up with this:

=SUMPRODUCT((B4:J4=TRANSPOSE({"Ford","Chrysler"})))

Which seems to work perfectly, but now my 2nd issue. The TRANSPOSE formula needs to be a range of values. As an example:

=SUMPRODUCT((B4:J4=TRANSPOSE({(A23:A25})))

... but that give me an error as the TRANSPOSE needs to have an array function and the one present in in error.

Any ideas?

 

[Skolastix]

Oh ya - I forgot.... this works:

=SUMPRODUCT(($B$4:$J$4=TRANSPOSE(A23:A24))*($B$9:$J$9))

but only gives me the 1st result from the transposed array (since it's not an array formula)

If you guys can help me get this, then I will FINALLY be able to have an unlimited amount of criteria (within the TRANSPOSE formula)

...no pressure LOL!!!

 

[KenWright]

So just array enter it?

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Skolastix]

Sorry Ken, I tried that before... it gave me a #N/A error message

I've also tried:

1){=TRANSPOSE(A23:A25}} (array entered) in one cell and referencing the cell in the SUMPRODUCT formula. Only got the 1st value answer like above

2)TRANSPOSE the $B$4:$J$4 instead and I'd get a 0 value

 

[KenWright]

Sorry, didn't realise your range was a row and columnar. Try this:-

=SUMPRODUCT(($B$4:$J$4=(A23:A24))*($B$9:$J$9))

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Skolastix]

Ken...that worked PERFECTLY!!!

Talk about the K.I.S.S. (Keep It Simple Stupid) theory!!! I guess I was trying everything else but that. Thanks!

One BIG star for you...

Stais

 

[KenWright]

You're welcome

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Skolastix]

Actually, since we're on a roll.. one last question to add the gravey on top...

In thee new equation: =SUMPRODUCT(($B$4:$J$4=(A23:A24))*($B$9:$J$9)) - if there is a "" cell between B4:J4 and say for example, A24 is blank (no criteria), the formula will pick up the value from ALL blank columns. I have alleviated this issue by making sure that for the criteria range, the user (or VB program) either enters a valid value or a "x" space-filler is added.

Do you know off any way to include a check in the formula which will ignore all columns with blank ("") values?

 

[KenWright]

Untested but how about this:-

=SUMPRODUCT(($B$4:$J$4=(A23:A24))*($B$4:$J$4<>"")*($B$9:$J$9))

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Skolastix]

And the Grand Prize goes to....

That worked perfectly Ken - once again, MANY thanks!

Stais

One last question...can you have the equation cook me dinner on command???? LOL!!!!

 

[KenWright]

Hmmm - let me think about the syntax for a bit

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------