SUMIFS(?) I need to sum data using both horizontal and vertical criteria

Hey everyone.
I have a huge data table, and I just can't seem to find a way to sum the numbers the way I want.

Here's an example of the table:

___ A B C A B C A B C A B C A B C
Jan 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5
Jan 3 4 5 6 3 4 5 6 3 4 5 6 3 4 5
Jan 3 4 5 6 3 4 5 6 3 4 5 6 3 4 5
Jan 3 4 5 6 3 4 5 6 3 4 5 6 3 4 5
Feb 3 4 5 6 3 4 5 6 3 4 5 6 3 4 5
Feb 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Feb 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Feb 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Feb 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Mar 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Mar 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
...
...

I apologize if that's hard to read..
But, in essence, I need to sum together every value that's in January AND is in a column labeled "A".

I could just write up a bunch of "sumif=" functions but I feel like there's a simpler way..
I tried using "sumifs=" but I don't think it can handle horizontal AND vertical criteria..

I appreciate any help that you guys can give!

Can you post a sample. I believe I know what you are talking about, but it would be easier if I could work with some data.

Like so:

=SUMPRODUCT((MONTH($A$2:$A$61)=1)*($B$1:$Y$1="A")*$B$2:$Y$61)

That's great JBeaucaire!

You typed it yourself instead of just cut/paste from the forum?

Are you wanting a formula or a macro?

Was posting same time as you.

Your screenshot shows you used a different range than the example you gave us, so the formula would have to be modified to fit your range.

Originally Posted by Xer2

Are you wanting a formula or a macro?

A formula would be preferable..
I have a gut instinct that there is some variation of the =SUM() function that can handle this..

But it's relatively easier doing this with a macro, right?

The formula JBeaucaire provided will work if you adjust your range to the actual range of your real workbook. I'll work on some macro and show you a possible example.

The formula given requires YOU to edit the left-hand range to match the rows with dates to search, the top-row range where the "letter codes" are, then the final range must be the table values that match the exact same number of rows and columns.

Based on the picture:

=SUMPRODUCT((MONTH($A$2:$A$12)=1)*($B$1:$P$1="A")*$B$2:$P$12)

Hi,

Thanks for the formula, i was in same trouble and formula is not working for me... In my data set, I have names in column A and sum is required for each team members for priority P0 and P1

Please see below sample:

Name P0 P1 P0 P1
Ankit 40 20 10 50
Shruti 60 10 10 10
Ankit 10 0 5 10
Rahul 60 10 10 10
Rahul 0 10 0 0

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.