Sumproduct based on Row and Column Criteria

ac3100

Board Regular
I have 50 rows of data

Down Column A is a region number(1-5) in Cells A2:A51

Across Row 1 is the date in MMMYY format in cells B1:Y1

In the Matrix is an idicator of "X" if a criteria is met

I simply want to count the number of "X" for each Region and Month

I tried: =SUMPRODUCT(--(A2:A51="1"),--(B1:Y1="MAR09"),B2:Y51="X")

Somehow I think I need to index the columns going across to get this to work.

I am using Excel 2003

Last edited:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Jonmo1

MrExcel MVP
maybe like this

=SUMPRODUCT((A2:A51=1)*(TEXT(B1:Y1,"MMMYY")="MAR09")*(B2:Y51="X"))

ac3100

Board Regular
The formula gives me a numeric answer which is a good start, however when I reference MAR09, I get 0 and there are 8 X's so the answer should be 8.

When I reference MAR10 I get an answer of 18 and there are 10 X's so the answer should be 10.

It looks like its counting both MAR09 and MAR10 together when I reference MAR10 and Doesn't count anything when I refernce MAR09.

ac3100

Board Regular
I removed the Text part of the formula and now it works properly.

Thank you!

Replies
1
Views
277
Replies
5
Views
129
Replies
10
Views
675
Replies
3
Views
107
Replies
1
Views
92

1,127,028
Messages
5,622,287
Members
415,890
Latest member
macak333

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.

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

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