Excel - Summing multiple rows of data on separate tab

[RyanScharfy]

I don't thing Vlookup or SumIf will work. Here's the example.

Tab 1

Column A Column B Column C
Color A or B Amount
Red A $100
Red A 200
Red B 50
Blue A 60
Blue A 40
Blue B 100

In Tab 2 I'd like to have a cell which adds all the A rows where Blue is the Color (the result would be $100)

Tab 1, though is variable. Every time I do the report there may be more or less rows with different combinations.

Anybody have any ideas?

 

[SkipVought]

Hi,

Check out the PivotTable Wizard. This took me about FIVE SECONDS
[tt]
Sum of Amount A or B
Color A B Grand Total
Blue $100.00 $100.00 $200.00
Red $300.00 $50.00 $350.00
Grand Total $400.00 $150.00 $550.00

[/tt]


Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[RyanScharfy]

Thanks Skip. Unfortunately I've given you the "simplified" example. In my situation I can't use a pivot table. This has to be easy enough to simply drop in the data and let the summary tab do all the work.

 

[bluedragon2]

=SUMPRODUCT((tab1!a1:A100="BLUE")*(tab1!B1:B100="A")*(tab1!c1:c100))

[Blue]Blue[/Blue] [Dragon]

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

 

[SkipVought]

And the REASON that you can't use a PT is...???

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[RyanScharfy]

Hi Skip,

To answer your question, the summary sheet has many items that aren't possible/appropriate if I used a pivot table on that tab. The end users are dictating to me the format and layout of the summary sheet.

 

[RyanScharfy]

BlueDragon,

Something tells me this is the right track, but the actual formula returns zero if I use your example combined with my example data.

 

[bluedragon2]

I put BLUE, you may want to change that to Blue

[Blue]Blue[/Blue] [Dragon]

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

 

[RyanScharfy]

Bluedragon,

Bingo. It wasn't that. I was using quotes on a number in my actual spreadsheet, which the formula didn't like.

EUREKA!