trying to avoid excessive concatenate statements

Hello. I use sumifs and countifs on summary pages often, and I find that I always need to create a column with concatenated in my main sheet. I'm wondering if there's a way to avoid this.

For example: on my summary page, I have years as column headers and event titles as rows. I want to create summary data showing the sum of people in each event for each year. What I would do is create a "Event name - Year" concatenated column on my data page, and then use a countif like this:

=countif([Event name - year column],concatenate(B$1,$A2))

Any ideas? When my spreadsheets get large, I end up with so many concatenated columns.

Thanks so much.
Amanda

Thanks for your reply. I know the shorthand, but I don't think that would reduce the number of concatenated columns that I need to create on my main data page. Would it? Maybe I'm misunderstanding your suggestion.

There is an alternative to COUNTIF, that I have seen many times on this forum, using SUMPRODUCT.
Try:
=SUMPRODUCT(--(YearColumn=A2),--(EventColumn=B1))

The (YearColumn=A2) bit gives an array of TRUE or FALSE values for each entry in the YearColumn range.
Similarly for (EventColumn=B1).

The trick is in the - signs.
EXCEL stores logical values internally as 1 for TRUE and 0 for FALSE.
By treating logical values as numbers you can "trick" Excel into converting the arrays of TRUE and FALSE into arrays of 1 and 0.
(the first - sign gives arrays of -1 and 0, the second - sign converts the -1s to 1s)

Now SUMPRODUCT adds the result of multiplying each element of the first array with the coresponding element of the second.
Note that the result of multiplying any two elements of these arrays will only be 1 if both the elements are 1, otherwise it will be zero.
So adding the results of the multiplications gives you the number of times you get a match in both columns simultaneously.

Mark.

PS.
I've perhaps gone over the top a bit in my explaination, but I think this is a clasic little trick and I love it!

I can't wait to try this. This may save me many hours and many MBs of space!!

Thanks!

Okay, that works for my countifs (hooray)... is there anything similar for sumif?

Thanks!

Try:
=SUM((B1:B10)*((A1:A10)=C2)) (careful with placing all the brackets...)

NOW instead of just pressing the Enter key after typing in the formula press the Ctrl Shift and Enter keys together.

Note that the formula is now shown as:
{=SUM((B1:B10)*((A1:A10)=A2))}
with curly brackets round it.
This tells Excel to treat it as an "Array Formula" so SUM expects to be given an array containing many values (rather than a several individual values).

Array Formulas are a bit odd, but they are worth getting into if you are going to do a lot of Excel work.

Mark.

You can easily extend SUMPRODUCT to sum a range based on multiple conditions, this way you can avoid CSE formulas. Amending Mark's example

=SUMPRODUCT(--(YearColumn=A2),--(EventColumn=B1),SumColumn)

This sums one column based on the criteria in two others