**Tom’s Tutorials For Excel: Using SUMPRODUCT on Multiple Columns**

Most examples of the SUMPRODUCT function show a single list of numbers being evaluated for a particular criteria. The following 6 pictures show a simple modification involving SUMPRODUCT, to demonstrate some versatility with that function.

The first picture shows a range of monthly sales of a few warehouse items that are listed as data validated criteria for cell A2. Here, I selected the criteria SKU 567GHI.

In the next picture, cell D2 sums the numbers to 6021 for January, February, and March that belong to SKU 567GHI with this formula that simply includes columns B, C, and D for the SUMPRODUCT range of inclusion:code>=SUMPRODUCT((A7:A100=A2)*B7:D100)

The next picture shows 567GHI summed to 6507 in cell F2 for April, May, and June with this formula:`=SUMPRODUCT((A7:A100=A2)*E7:G100)`

The next picture shows 567GHI summed to 6433 in cell H2 for July, August, and September with this formula:`=SUMPRODUCT((A7:A100=A2)*H7:J100)`

The next picture shows 567GHI summed to 8614 in cell J2 for October, November, and December with this formula:`=SUMPRODUCT((A7:A100=A2)*K7:M100)`

Finally, SKU 567GHI’s total of 27575 in cell M2 for all 12 months is returned by this formula:`=SUMPRODUCT((A7:A100=A2)*B7:M100)`

Posted in Tom's Tutorials for Excel

Tags: Formulas Functions

Tags: Formulas Functions

- There is a requirement to create columns dynamically and the number of columns would come from some other variable. Actually, I need to split comma-delimited string values (which is dynamic again) into columns. – Muskaan Sep 11 '17 at 15:15.
- Take note of the third column 'colspan' rather than 'rowspan'. I was confused about why this works at first. – MUlferts Feb 21 '19 at 14:02.
- Reformat documents, i.e. Reformats multiple column pages to be more readable with speech. InsertLeft Arrow + F1 Help with current element Navigation Other Commands Forms Command Description Up Arrow Say Prior Line Down Arrow Say Next Line Left Arrow Say Previous Character Right Arrow Say Next Character Num Pad 5 Say Character.

Lines 97-98 set rngSrc, the column of data from our Source Worksheet. Since we know the column number (lngIdx, as we’re looping through the columns) as well as the last-occupied row on the Source Worksheet (lngLastSrcRowNum), we can create this Range using these cells. Having either all columns or a single column returned back has limited utility. 1:33 Remember, the power of SQL is that you can retrieve any subset of information 1:38.

timopeanuhockpocidexckindgrosewat.co – 2021