sum if rows contain same data

[alexander.small]

Hi,

I have a table of data that has 800+ entries. A lot of the entries have the same information in the first 3 columns but the fourth column (D), showing hours, has a different number than the last.
I need a formula to add up the hours if the data in the first 3 columns is exactly the same. If one of the column's data is different then it needs to remain as an independant entry.

If it would be possible to automatically show the formulated data like I have done in my example table then this would be a bonus!

Thanks,
Alex

[TMS]

Have you looked at SUMIFS, or possibly a Pivot Table?


Regards, TMS

[Punnam]

HI

=SUMIFS($D$3:$D$36,$A$3:$A$36,F3,$B$3:$B$36,G3,$C$3:$C$36,H3)

Punnam

[Richard Buttrey]

Hi,

See attached,

I had originally thought you had added the values in F:H and therefore used the SUMIFS() function you see in I4. Then I realised that this was a classic candidate for a Pivot Table.

[alexander.small]

HI

=SUMIFS($D$3:$D$36,$A$3:$A$36,F3,$B$3:$B$36,G3,$C$3:$C$36,H3)

Punnam

Punnam, that formula requires the data that is in the table that I have typed out manually. This is a much shorter version of the table that Iwant, as it shows only the totals.
I need to get to that result, so unfortunately I don't have that table at the moment so I can't use your formula.

[alexander.small]

Hi,

See attached,

I had originally thought you had added the values in F:H and therefore used the SUMIFS() function you see in I4. Then I realised that this was a classic candidate for a Pivot Table.

Hi Richard, this is really what I need but I'm not sure how to get the pivot table to display like this...as I can only get it to list everything below each other.

Could you please explain?

thanks!

[Richard Buttrey]

By default when you create a pivot table you get the 'new look'. i.e. New since Excel 2007. Not only does this list the field values in a single column it also defaults to adding a Sub total value for each field.

I think you're looking for the old 'Classic View' In which case right click anywhere in the PT and select Pivot Table Options...Display tab and select the 'Classic' PT.
You might also want to right click in a field and remove the tick on the 'Subtotal...field name'. Depends exactly what you want to see.

[alexander.small]

That's exactly what I was looking for, thank you very much.
I've been using Pivot Tables since February and never knew you could display the data like that!
They do make life a lot easier when you know how to use them.
Thanks again Richard!

[Richard Buttrey]

Hi,

Glad to have helped and thanks for the rep.

[Vikas_Gautam]

Hi bro,
got the solution..
check the attachment


Click * to appreciate

[Vikas_Gautam]

liked the solution or not...?

[alexander.small]

Hi bro,
got the solution..
check the attachment


Click * to appreciate

Hi Vikas,
Thanks for your reply but this also uses my example table which I have only mocked up to explain what I wanted. I won't have this information for the rest of the data...as that is what I am trying to get.

[Punnam]

Hi Richard, this is really what I need but I'm not sure how to get the pivot table to display like this...as I can only get it to list everything below each other.

Could you please explain?

thanks!

Hi,

I have attached u the updated sheet for u r reference .


Punnam

[morevijay1982]

boss use =+SUMPRODUCT(($B$3:$B$36=$G3)*($C$3:$C$36=$H3)*($D$3:$D$36))

[Rupeshpatil]

Can someone please explain me what is the purpose of putting "+" sign after "=" in a formula. I have seen many people using this but not get the approp answer yet.

Regards,
Rupesh

[popipipo]

The + - sign was needed at the Lotus 123 program, but unnecessary in Excel

[Vikas_Gautam]

oh man...
we usually start the formula using = and sometime..
we use + to start the formula...
when we use + then excel automatically puts a = sign before the plus sign..

click * to appreciate...

[Rupeshpatil]

Thank you Willem and Vikas for the answer. so there is no any special reason for putting this, both are same.

Rupesh

[rajansettu]

Hi Rupeshpatil,

Its a typo error and the formula is =SUMPRODUCT(($B$3:$B$36=$G3)*($C$3:$C$36=$H3)*($D$3:$D$36))

Try this.