Sum and count formula with multiple column and row

[Jhon Mustofa]

hello all,

i need something simple formula to sum and count my set data excel, with multiple criteria...
(multiple column and multiple row)....

here, 4 criteria have matching line row and column (location, model, range of year and level)...

and then expected sum and count start from in cell D12 down right (col. Y1, Y2, Y3)..

i am called "green area"

further information, please see my workbook upload........

for your helping, i say much appreciated and thanks very much....

john.m

[alvin-chung]

Hi John, I think D12 should be 22 instead of 26?



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[Ron Coderre]

Using your posted workbook...
This regular formula, copied across and down, returns the sum of values for the referenced Location, Model, Level, and Year

Please Login or Register  to view this content.

Is that something you can work with?

[Jhon Mustofa]

you are right, Alvin...

D12 should be 22....i missing it.

[alvin-chung]

I mean 20 because you need to matched the model too
Try this formula in D12

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[Jhon Mustofa]

ups...sorry Alvin, D12 should be 20, cause location GD 01, with model RI-221

[Kaper]

How about sumproduct only, without index:

Please Login or Register  to view this content.

[Jhon Mustofa]

for Ron and Alvin, thanks for your helping.....IT'S 100% WORKING..

my problem is SOLVED............

[alvin-chung]

Thank you for your feedback, you're most welcome



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[Ron Coderre]

@Kaper
Yes, that works...
Odd, it didn't work when I originally tried it. (I must have had a typo) :\

[Jhon Mustofa]

ups...sorry, i am missing

Ron and Alvin, in my OP#1, how about COUNT...

maybe...using "COUNTIF" or anything...

Could you help me...........please...

[alvin-chung]

HI John, try this array formula in D12 (produce array formula by pressing ctrl+shift+enter)

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[Jhon Mustofa]

thanks Alvin, i am very very happy.......

[alvin-chung]

You're welcome, I'm glad it helps



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin