Sumproduct based on Row and Column Criteria

ac3100

Board Regular
Joined
Aug 21, 2002
Messages
185
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")
The answer should = 3

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

I am using Excel 2003
 
Last edited:

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
maybe like this

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

ac3100

Board Regular
Joined
Aug 21, 2002
Messages
185
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
Joined
Aug 21, 2002
Messages
185
I removed the Text part of the formula and now it works properly.

Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,534
Messages
5,596,732
Members
414,093
Latest member
Tegglet

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