SUMPRODUCT using criteria over mulitple rows across alternate columns

[jawilliams29]

I have a small spreadsheet which records staff hours in two different categories (Tch and RD) over the weeks in a year. Each staff member has a category e.g. TIC3, TIC6, HTB1 or HTB2. I want to be able to add up the totals for each staff cateogry for each work category. E.g. The total hours for TIC3 staff who did Tch hours. The spreadsheet looks like this:
A B C D E F G H I J ....
1 Tch RD Tch RD Tch RD Tch RD
2 John TIC3 2 1 3 1 4 1 2 2
3 Mary HTB1 1 3 1 4 1 2 2 5
4 Joe TIC6 4 1 2 2 5 6 2 1
5 Phil TIC3 2 2 5 6 2 1 4 3
6 Julie HTB1 6 2 1 4 3 5 1 2
....

Tch Hours
TIC3 = (2 + 3 + 4 + 2) + (2 + 5 + 2 + 4)
TIC6 = 4 + 2 + 5 + 2
HTB1 = (1 + 1 + 1 + 2) + (6 + 1 + 3 + 1)
HTB2 = 0
Then the RD hours would be the opposite alternate columns.

I have looked at the SUMPRODUCT with the MOD(ROW) and MOD(COLUMN) options but I cannot seem to find a way for it to look at more than one row or column at a time.

I have always found this site really helpful when I am stuck, but it seems no one else has ever had this problem.

Thank you

 

[luis55]

The solution Aladyn gave worked! This was my most challenging Excel problem so far, and the reply was immediate. Thank you very much!

 

[T. Valko]

I need the criteria, because I have let's say 125 food items (125 rows in column A) grouped in 9 families (meat, fish, milk, cereals etc)(125 rows in column B) and I want to know how much of each of the 9 families I eat every day of the month (so I can make a monthly graph). I could send you the file if I would find a button where I could send it to you.

If this is your data:

Book1
	A	B	C	D	E	F
1	_	1	1	1	2	2
2	Meat	_	L	_	L	D
3	Meat	_	_	L	D	_
4	Fish	L	D	_	D	_
5	Fish	_	_	_	_	_
6	Fruit	_	_	_	_	_
Sheet1

What exactly do you want to count?

 

[Aladin Akyurek]

The solution Aladyn gave worked! This was my most challenging Excel problem so far, and the reply was immediate. Thank you very much!

You are welcome. Thanks for providing feedback.

 

[luis55]

Day 1: 2 portions of meat, 2 portions of fish
Day 2: 3 portions of meat, 1 portion of fish

if I use =COUNTIF(A2:F6,"<>") I will get the count of portions for day 1 and day 2 combined, for all types of food combined (meat, fish and fruit) but I want the count separately, ie, how many portions by type of food by day.

My spreadsheet has 240 food items (240 cells in column A) and each item belongs to a food group (I have 16 food groups – cells in column B): example:
A2:A242 --- B2:B242
chicken (food item)– meat (food group)
codfish - fish
cucumber - vegetable
hake – fish
kiwi - fruit
mango – fruit
soup - vegetable
tofu - vegetable
turkey – meat
etc
I have two ranges with data, (1) one (let’s call it “raw data range”) with cells for each food item (and the food group on the neighbouring cell) in range A2:B242, and 100+ columns for the 31 days of the month, (with repetition of days) C1:CQ1; the data is entered on range C2:CQ242; and adding new food items every month. (2) The other range is the “summary range” where I write the formulas to calculate the total portions taken daily for each food group. This is the data I will use for the graph.

 

[T. Valko]

Day 1: 2 portions of meat, 2 portions of fish
Day 2: 3 portions of meat, 1 portion of fish

if I use =COUNTIF(A2:F6,"<>") I will get the count of portions for day 1 and day 2 combined, for all types of food combined (meat, fish and fruit) but I want the count separately, ie, how many portions by type of food by day.

My spreadsheet has 240 food items (240 cells in column A) and each item belongs to a food group (I have 16 food groups – cells in column B): example:
A2:A242 --- B2:B242
chicken (food item)– meat (food group)
codfish - fish
cucumber - vegetable
hake – fish
kiwi - fruit
mango – fruit
soup - vegetable
tofu - vegetable
turkey – meat
etc
I have two ranges with data, (1) one (let’s call it “raw data range”) with cells for each food item (and the food group on the neighbouring cell) in range A2:B242, and 100+ columns for the 31 days of the month, (with repetition of days) C1:CQ1; the data is entered on range C2:CQ242; and adding new food items every month. (2) The other range is the “summary range” where I write the formulas to calculate the total portions taken daily for each food group. This is the data I will use for the graph.

Try this...

Book1
	A	B	C	D	E	F
1	_	1	1	1	2	2
2	Meat	_	L	_	L	D
3	Meat	_	_	L	D	_
4	Fish	L	D	_	D	_
5	Fish	_	_	D	L	_
6	Fruit	L	L	D	_	D
7	_	_	_	_	_	_
8	_	_	_	_	_	_
9	_	1	2	_	_	_
10	Meat	2	3	_	_	_
11	Fish	3	2	_	_	_
12	Fruit	3	1	_	_	_
Sheet1

This formula entered in B10:

=SUMPRODUCT(($A$2:$A$6=$A10)*($B$1:$F$1=B$9)*($B$2:$F$6<>""))

Copy across to C10 then down to B12:C12.

Of course, you would need to adapt it to your actual ranges.

 

[luis55]

It works nicely! Thank you very much! Now I have two solutions, SUM(IF and SUMPRODUCT.

 

[T. Valko]

It works nicely! Thank you very much! Now I have two solutions, SUM(IF and SUMPRODUCT.

You're welcome. Thanks for the feedback!