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

[johnnyL]

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)))

 

[Habtest]

Does this work

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Pens	10		A	10		B	20		C	40
2	B	50		C	50		C	20
3	C	40					A	10
4							Pens	5
Sheet1

Book1
	A	B	C
1	Pens	2	15
2	A	2	20
3	B	2	70
4	C	4	150
Sheet2

Cell Formulas
Range	Formula
B1:B4	B1	=COUNTIF(Sheet1!$A$1:$K$5,A1)
C1:C4	C1	=SUMIF(Sheet1!$A$1:$J$5,A1,Sheet1!$B$1:$K$4)

 

[johnnyL]

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]

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
	A	B	C	D	E	F	G	H	I	J	K
1	Pens	10		Markers	20		Pencils	20		Erasers	10
2	Pencils	30		Paper	100		Staples	1000
3	Erasers	30					Markers	10
4							Pens	5
5							Erasers	30
Sheet1

Book1
	A	B	C
1	Pens	2	15
2	Pencils	2	50
3	Markers	2	30
4	Erasers	3	70
5	Paper	1	100
6	Staples	1	1000
Sheet2

Cell Formulas
Range	Formula
B1:B6	B1	=COUNTIF(Sheet1!$A$1:$ZZ$5,A1)
C1:C6	C1	=SUMIF(Sheet1!$A$1:$ZY$5,A1,Sheet1!$B$1:$ZZ$4)

 

[johnnyL]

Thank you so much @Habtest. That works great.