Count and sum various cells across a range in Sheet1 based on a range of values in Sheet2

johnnyL

Active Member
Joined
Nov 7, 2011
Messages
470
1) I have a Column (Range) of values in Sheet2 ... The A column to be exact
2) Need to loop through that Column (Range) from Sheet2 of values to find, and 1) determine the number of matches found in a range from Sheet1, Store the matches amount found in the B Column of Sheet2, just to the right of the value that was searched for in Column A of worksheet2, and 2) if a match is found, add the additional quantity amount, which is found one cell to the right of the searched/found value from Sheet1, to a running total for each value from the Sheet2 values being searched for and store the total in the C column of Sheet2, 2 cells to the right of the value that was searched for in Column A of worksheet2.

The dillemna I am facing, and haven't encountered previously, is that the range being searched through on Sheet 1 is not just two columns side by side, it consists of various columns side by side, for example, Value/Quantity/Empty column/Value/Quantity/Empty Column, ETC. ie. sets of three columns. The number of Values/Items in each column in the range to be searched on Sheet1 is variable, Some sets of three columns may only have 2 values/quantities, some may have three, four or five or more. Future additional info of the three columns is added to the right of the previous info stored on Sheet1, aka it is a living/breathing document that expands three columns wide at a time each time additional supplies are added.


I'm not sure how to use the installed XL2BB so I will just post some pics.

Cell formulas and/or VBA macro code is fine.


The cell formula I have used thus far to capture the amount of matches for each searched value is:
Formula for Sheet2 Range B1-B6:
=COUNTIF(Sheet1!A$1:Sheet1!K$5,INDIRECT(ADDRESS(ROW(),COLUMN()-1)))
 

Attachments

  • Sheet1Example.PNG
    Sheet1Example.PNG
    7.3 KB · Views: 5
  • Sheet2Example.PNG
    Sheet2Example.PNG
    10.7 KB · Views: 4
  • DesiredSheet2Result.PNG
    DesiredSheet2Result.PNG
    10.5 KB · Views: 5

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Habtest

Board Regular
Joined
Jul 30, 2020
Messages
168
Office Version
  1. 2016
Platform
  1. Windows
Does this work
Book1
ABCDEFGHIJK
1Pens10A10B20C40
2B50C50C20
3C40A10
4Pens5
Sheet1

Book1
ABC
1Pens215
2A220
3B270
4C4150
Sheet2
Cell Formulas
RangeFormula
B1:B4B1=COUNTIF(Sheet1!$A$1:$K$5,A1)
C1:C4C1=SUMIF(Sheet1!$A$1:$J$5,A1,Sheet1!$B$1:$K$4)
 

johnnyL

Active Member
Joined
Nov 7, 2011
Messages
470
Thank you @Habtest for your consideration.

Unfortunately, your results do not match what I posted for DesiredResults. It appears that you have changed 'Book1' aka Sheet1, but I desire Sheet1 to be left alone and results be saved into Sheet2 columns A,B,C
 

Habtest

Board Regular
Joined
Jul 30, 2020
Messages
168
Office Version
  1. 2016
Platform
  1. Windows
Have you tried it in your sheet? Though I didn't transcribe the whole data from pictures, the formulae should serve.
Here is the result based on your data. I have changed the columns breadth to accommodate future addition, you may change the range accordingly.
Book1
ABCDEFGHIJK
1Pens10Markers20Pencils20Erasers10
2Pencils30Paper100Staples1000
3Erasers30Markers10
4Pens5
5Erasers30
Sheet1

Book1
ABC
1Pens215
2Pencils250
3Markers230
4Erasers370
5Paper1100
6Staples11000
Sheet2
Cell Formulas
RangeFormula
B1:B6B1=COUNTIF(Sheet1!$A$1:$ZZ$5,A1)
C1:C6C1=SUMIF(Sheet1!$A$1:$ZY$5,A1,Sheet1!$B$1:$ZZ$4)
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,133,271
Messages
5,657,760
Members
418,411
Latest member
Excellency

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top