SUMPRODUCT Function 1

[Suggie]

Hello,

I am using this formula =SUMPRODUCT(($B$14:$B$16=$B$5)*($C$13:$F$13=C4)*$C$14:$F$16)

In the below summay table which returns the sum of the criteria in Data Source A

B5 = the Cost Code 1 and C4 = Company A and C14:F16 is the table Data Source B


Criteria is that Cost Code and Company Name match that reurn the sum of the values.

I want to include values in Data Source B also in the summary table, I've tried adding +SUMPRODUCT but this doesn't work....any ideas on how I can include the second table in the summary table?

TIA,
Mark




Summary Table

Cost Code Company A Company B Company C Company D Company E Company F
1 70
2
3
4
5

Data Source A

Cost Code Company A Company B Company C Company F
1 50 108 50 200
2 70 22 90 40
1 20 50 80 20

Data Source B

Cost Code Company D Company E
4 70 22
1 20 50
2 90 40
5 50 200

 

[bluedragon2]

On your first formula change

=SUMPRODUCT(($B$14:$B$16=$B$5)*($C$13:$F$13=C4)*$C$14:$F$16)

to

=SUMPRODUCT(($B$14:$B$16=$B5)*($C$13:$F$13=C$4)*$C$14:$F$16)

and when you add the second one, follow the same format.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Suggie]

I've tried the below and I get #VALUE!

=SUMPRODUCT(($B$14:$B$16=$B5)*($C$13:$F$13=C$4)*$C$14:$F$16)+SUMPRODUCT(B21:B24=B5)*(C2020=C4)*C2124)

Any ideas, first time using this function (as you may have gussed)

TIA

 

[GlennUK]

Suggie, that will work for Company A, Cost code 1 only, read BlueDragons posting, and put your $ signs where suggested.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[TonyJollans]

You do need to get the $ signs right for it to work in general, but in the specific, you're missing a left parenthesis ..

=SUMPRODUCT(($B$14:$B$16=$B5)*($C$13:$F$13=C$4)*$C$14:$F$16)+SUMPRODUCT[highlight]([/highlight](B21:B24=B5)*(C2020=C4)*C2124)

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Suggie]

Thanks folks - I've given Blue the Star but all help appreciated.

Cheers folks,

 

[hrm1220]

OK, I've looked at all the sumproduct threads and tried them all. I can't figure out what I'm doing wrong.

Here's the data

Animal&Type Rate Service Type Animal
DogNew 9.16 0.042 New Dog
DogNew 9.16 0.004
CatNew 9.16 0.042
DogNew 9.16 0.49

Note:columns F and E have the Type and Animal split out(this is the criteria)

I should get an answer of 4.90976.
formula:
=SUMPRODUCT((A2:A7=F2&E2)*(B2:B5)*(C2:C5)
which of course gives me #N/A". I've even tried
=SUMPRODUCT((A2:A7=F2&E2)*(B2:B5))+SUMPRODUCT((A2:A7=F2&E2)*(C2:C5))
with the same results.

Thanks for your help.

 

[KenWright]

Please post new questions in a new thread. That having been said:-

All ranges in the SUMPRODUCT formula need to be the same size. Also you missed off the final parenthesis in your first formula.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[hrm1220]

Ken, thanks for the info it works for my hypothetical scenario, but not my actual data. I'll post a new one with my actual data.