# Is there a function to multiple same row of two tables? MMULT does not

Copper Contributor

# Is there a function to multiple same row of two tables? MMULT does not

Is there a function to multiple cell for cell of two Tables? Otherwise, to use MMULT, my understanding is I need to transpose; if so, wouldn't date information across a row of columns become unconventional (vs a column of dates)?

I prefer a function similar to MMULT but different

EXAMPLE (screenshot below)

Table B is my data entry; Headers of Multiple Foreign Currencies; 12 rows (Jan-Dec).

Is there a function to multiple Rate to each Foreign Currency without Transposing?

To use MMULT, do I not have to transpose, say Table A?

1. Headers would become a Column (a row for each Rate)
2. Dates would become Headers (a column for each Month)

IMHO, it is preferable to keep conventional view of Historical Exchange Rates; that is without transpose.

4 Replies

# Re: Is there a function to multiple same row of two tables? MMULT does not

Don't you want a simple SUMPRODUCT?

# Re: Is there a function to multiple same row of two tables? MMULT does not

@Hans Vogelaar pls correct me if wrong, SUMPRODUCT provides one output whereas MMULT provides column of outputs?

For Tax reporting, I need a column of months and next to it, column of CAD calculated.

# Re: Is there a function to multiple same row of two tables? MMULT does not

Your second screenshot shows a single cell with a #VALUE! result. I guess I don't understand what you want.

Could you attach a small sample workbook demonstrating the problem (without sensitive data), and an indication of the desired output, or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

# Re: Is there a function to multiple same row of two tables? MMULT does not

To multiply each deposit by the corresponding exchange rate would require a straightforward multiplication.

``= rates * deposits``

To sum the amounts held in each currency, you could use MMULT

``= MMULT(rates * deposits, {1;1;1})``

or you could use a 365 helper function to sum across rows

``= BYROW(rates * deposits, LAMBDA(x, SUM(x)))``

In the near future this last formula will reduce to

``= BYROW(rates * deposits, SUM)``

which is somewhat less intimidating.