Faster way to sum rows and columns that match a specific criteria

[theyee02]

Hi, I have a data set with repeating row headers and unique column headers. I am trying to sum the data into unique row headers.

My current solution is to use {=IF(ROW LIST=NAME,IF(COLUMN LIST=DATE,DATA RANGE,0),0)} (CTRL + SHIFT +ENTER)
However, this formula can be slow if used across a large number of cells. Is there some form of sum index match or whatnot to make this work?

Date 1 Date 2 Date 3

aa 691 691 691
bb 302 302 302
cc 84 84 84
dd 22 22 22
ee 10 10 10
ff 97 97 97
aa 329 329 329
bb 619 619 619
cc 542 542 542
dd 237 237 237
ee 66 66 66
ff 18 18 18

Date 1 Date 2
aa
bb
cc
dd
ee
ff

[AliGW]

Have you tried SUMIF?

[theyee02]

That works well too, but what I'm trying to do is be able to automatically match the column header. SUMIFS rely on correct positioning and since the data I'm pulling is from another worksheet, I want to be sure it grabs the correct column, as I won't be the only one in the file and don't want to double check positioning every time.

[Glenn Kennedy]

It's imnpossible to visualise this from the poor layout that the forum platform applies.

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[theyee02]

Sure, no problem. So to reiterate, I'd like to get these results without using SUMIF(s) because I'd like to match dates, and avoid using =SUM(IF( because that'll end up with long calc times over big data sets. Thank you very much.

[Glenn Kennedy]

Is this it? In J4, copied across and down:

=SUMPRODUCT(($A$3:$A$26=$I4)*($B$2:$F$2=J$3)*$B$3:$F$26)

[AliGW]

In J4 copied across and down:

=SUMPRODUCT(($B$2:$F$2=J$3)*($A$3:$A$26=$I4),$B$3:$F$26)

[theyee02]

I have read that SUMPRODUCT is calc intensive in big data sets. Are there any other methods that would be faster, or is this the best option?

[Glenn Kennedy]

How big is big? It'll certainly be faster than your clunky array....

[CK76]

I'm not sure how much more efficient it's going to be. But if you want to avoid SUMPRODUCT...

Try...
=SUMIFS(INDEX($B$3:$F$26,,MATCH(J$3,$B$2:$F$2,0)),$A$3:$A$26,$I4)

Copy across and down.

Basically index is used to return entire column where date matches.

[theyee02]

I'm not sure how much more efficient it's going to be. But if you want to avoid SUMPRODUCT...

Try...
=SUMIFS(INDEX($B$3:$F$26,,MATCH(J$3,$B$2:$F$2,0)),$A$3:$A$26,$I4)

Copy across and down.

Basically index is used to return entire column where date matches.

Yes! This is what I was looking for! Thank you CK76! I like to evaluate formulas and since SUMPRODUCT uses lots of true/falses, I disklike it. The worksheets communicate with the server, so any increase in efficiency will be helpful.

Thanks everyone that helped me with this.

[AliGW]

You specifically said at the top of the thread, when I asked that question, that you did not want to use SUMIFS, so it was avoided!

Glad you have your resolution, though.

[theyee02]

Ah, I get what you're saying. My intention was to say that I wanted more than just the SUMIFS, because SUMIFS by itself doesn't match columns, only rows. I wanted the tracking ability of index match with the summing ability of sumifs or just summing. Sorry for the confusion. I'm pretty ecstatic about this new use of SUMISF + INDEX/MATCH though. Thank you for helping me work it through.