Sumproduct help

[hrm1220]

I have a main sheet that I'm trying to add up the total costs for animal types that have been billed. So on the Main sheet I have the total of cost by animal type and id (as shown below):

ID	Animal	total cost
1234	Cat	11
1235	Dog	12.27
1236	Cat	18
1237	Bird	21
1238	Cat	23

Also, on the Main Sheet is a summary of all the types of animals and gives a total cost of only the ones that have been billed.

Main Sheet
		
Summary		
	Animal	Cost
	Cat	52
	Dog	12.27
	Bird	21

In the "Cost Sheet" I have the following information:

Cost sheet			
ID	Animal	Cost	Billed
1234	Cat	5	yes
1235	Dog	5.27	yes
1236	Cat	10	yes
1237	Bird	12	yes
1238	Cat	13	yes
1234	Cat	6	yes
1235	Dog	7	yes
1236	Cat	8	yes
1237	Bird	9	yes
1238	Cat	10	no

so this is what I've come up with and I can't seem to get the correct answer (ie - cat should be 42)

=SUMPRODUCT(--(('Cost'!B2:H272=B16)*('Cost'!H2:H272="yes"))*'Main Sheet'!I30:I300)

Thanks for your help.

 

[Scott02]

A pivot table would give you a quick summary of all types and for either billed, unbilled, or total depending on which you choose. Place "Billed" in the Page area, Animal in the Row area, and (Sum of)Cost in the Data Area.

Hope this helps.
Scott

    Billed yes

Sum of     Cost
  Animal Total
    Bird 21
    Cat  42
    Dog  12.27
Grand Total 75.27

 

[doxiepup]

also DSUM can be used to add a certain column based on criteria in another column.

 

[AnotherHiggins]

I don't know what information is in:
[ul][li]'Cost'!B2:H272[/li]
[li]B16[/li]
[li]'Cost'!H2:H272[/li]
[li]'Main Sheet'!I30:I300[/li][/ul]

But at first glance, I'd say to try a format like this:
=SUMPRODUCT(--('Cost'!B2:H272=B16)*--('Cost'!H2:H272="yes")*--('Main Sheet'!I30:I300))

EXCEPT THAT you have varrying ranges in your portions of sumproduct. You'll need to have the same number of cells selected in each of the parts of the sumproduct function. Have a look at Sumproduct in Excel's help file.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[hrm1220]

Thank you all for your inputs.. Unfortunately I can't modify the format of the Main Sheet so a pivot table won't work.
John, I tried your suggestion and I couldn't get it to work I still come up with 52 instead of 42. And the information I showed is just an example I have quite alot of data.

Doxiepup I'm not familiar with Dsum, can you give an example of how it is used?

 

[hrm1220]

or would creating a function be easiest?

 

[AnotherHiggins]

Perhaps it is just me, but since no one else has answered in a while I'll just throw this out there....

I have no idea what you want your function (AKA formula[red][sup]*[/sup][/red]) to do. As I said in my previous post, you haven't told us what information is in what field. It isn't immediately obvious given the truncated example data you have provided.

Please back up, keep in mind that no one here reads minds, and restate your question so that the simplest of us can understand what it is you want to accomplish.

[red][sup]*[/sup][/red]I think you mean 'Macro' when you say "or would creating a function be easiest" since what you have going is a function.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[KenWright]

There are only two ways to realistically tackle this. Ideally you use a Pivot table, but you have discounted this. That leaves you sumproduct. This DOES work, and if you are getting strange results then you have likely done something wrong with the formula.

Give us the exact sheet names, and excat ranges of your data and we can give you the right formulas to use.

That having been said, assuming your data is as follows:-

Cost sheet
B C D E
ID Animal Cost Billed
1234 Cat 5 yes

Data on this sheet above is in the range B2:E272

Data on main sheet is as follows

A B
1 Animal Cost
2 Cat 52
3 Dog 12.27
4 Bird 21

To get a sum of all Cost values for Cat where billed = yes, then in cell B2 put the following formula and then copy down:-

=SUMPRODUCT(--('Cost'!$B$2:$B$272=A2),('Cost'!$C$2:$C$272),--('Cost'!$D$2:$D$272="yes"))

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

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

 

[KenWright]

Dohhh - Make that:-

=SUMPRODUCT(--('Cost'!$C$2:$C$272=A2),('Cost'!$D$2:$D$272),--('Cost'!$E$2:$E$272="yes"))

Does ID not play any part here?

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

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

 

[hrm1220]

Main Sheet:


Summary
row#15 Animal Cost
16. Cat 52
17. Dog 12.27
18. Bird 21

row# ID    Animal    total cost
30. 1234    Cat    11
31. 1235    Dog    12.27
32. 1236    Cat    18
33. 1237    Bird    21
34. 1238    Cat    23

Cost Sheet:

row#1 ID Animal Cost Billed
2. 1234 Cat 5 yes
3. 1235 Dog 5.27 yes
4. 1236 Cat 10 yes
5. 1237 Bird 12 yes
6. 1238 Cat 13 yes
7. 1234 Cat 6 yes
8. 1235 Dog 7 yes
9. 1236 Cat 8 yes
10. 1237 Bird 9 yes
11. 1238 Cat 10 no

So, the id number is only used for the "Main Sheet" to sum up the cost for the id's, but for the Summary on the "Main Sheet" I need only the sum of the billed animal types(which is in the "Cost" sheet) . I don't think the sumproduct will work since in the "Main Sheet" is ordered one way and the "Cost" sheet has them ordered in a different way and I can't change the order. If this isn't still clear on what my objective is, I apologize and appreciate your input.

 

[KenWright]

Did you try the formula adjusting for your ranges? As long as the summary is simply to sum all the instances of cost in one column for 'cat' or 'dog' etc that are found on the cost sheet where the billed flag is also 'yes', then that is exactly what the formula will do, irrespective of any order

What are your ranges, ie more specifically, what is the exact range on the Cost sheet for:-

Animal - is it B2:B272
Cost - is it C2:C272
Billed - is it H2:H272

What is the exact range on your Summary sheet that holds the list of unique animal types, ie 'cat' 'dog' etc, eg is it B5:B25 or what?

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

 

[hrm1220]

so the ranges on the "Cost" sheet are as follows:
Animal - is B2:B272
Cost - is C2:C272
Billed - is H2:H272
and on the "Main" Sheet the ranges are as follows:

B30:B300

 

[SilentAiche]

hrm1220,

Have you in fact looked at the TT FAQs regarding SumProduct? Please do. If the FAQs don't help, post back. No one here is trying to evade you; but, you have to demonstrate some personal effort at solving your problem.

Best of Luck!
Tim

[blue]______________________________________________________________
I love logging onto Tek-Tips. It's always so exciting to see what the hell I
said yesterday. [/blue]

 

[hrm1220]

I appreciate the help Ken, but I ended up creating a function instead. I just couldn't get the sumproduct to work correctly.

 

[KenWright]

Sumproduct I sone of the most powerful functions that Excel has, but is simplicity to use once you understand the basics. It would be time well spent to have a few minutes looking through the explanation and overview on this page here:-

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

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

 

[hrm1220]

Thanks Ken. I'll look through it. I just thought I wasn't getting my problem across clearly and just found another avenue to get what I need done to meet my deadline.