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:

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
maybe like this

=SUMPRODUCT((A2:A51=1)*(TEXT(B1:Y1,"MMMYY")="MAR09")*(B2:Y51="X"))
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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
Back
Top