Subtotalling after multiple criteria has been met

C

Cristian

Hello. I'd appreciate a nudge in the right direction with this issue, as I've
been searching but I can't seem to solve this on my own.

I'm trying to create a formula that goes through a list of data, and
subtotals (sum) all entries only if columns A, B and C each equal a
particular text string. Each string is different. Column E is where it needs
to sum the totals from.

This is a sample of sheet A (raw data).

Market Type Job Total
Australia AA Pink Car AA 295
Australia AA Pink Car AA 99
Australia RR Pink Car RR 30
Australia AA AUS Test GPC AA 1357
Australia RR AUS Test RR 3
Australia AA Pink Car AA 99
Australia AA Pink Car AA 99
Australia ZZ AUS Test BB / ZZ 456
India ZZ India ZZ Cat 3
India ZZ India ZZ Mouse 9
India AA India Calcium AA 200



I've created a Sheet that has a table with the criteria, waiting for the
totals to be entered:

Australia AA Pink Car AA (formula).


I've tried playing around with SUMIF, and SUBTOTAL, tried nesting a
combination of IF and AND statements, but it's becoming apparent to me that I
have no idea of what I'm doing.

Any help would be much appreciated.

Thanks.

- Cristian.
 
T

T. Valko

Try this:

Use cells to hold the string criteria:

G1 = string1
H1 = string2
I1 = string3

Then:

=SUMPRODUCT(--(A1:A10=G1),--(B1:B10=H1),--(C1:C10=I1),E1:E10)
 
S

smartin

Cristian said:
Hello. I'd appreciate a nudge in the right direction with this issue, as I've
been searching but I can't seem to solve this on my own.

I'm trying to create a formula that goes through a list of data, and
subtotals (sum) all entries only if columns A, B and C each equal a
particular text string. Each string is different. Column E is where it needs
to sum the totals from.
Click to expand...

[snipped]

Have you tried a pivot table?
 
S

Shane Devenshire

Hi,

And the steps for a pivot table are:

Here are the basic steps for setting up a pivot table:

1. Select all the data with one row of titles and choose Data, PivotTable
and PivotChart Report
2. Click Next twice
3. Click Layout and drag the Market field button to the Row area
4. Drag the Job field button to the Row area
5. Drag the Type button to the Row area
6. Drag the Total field button to the Data area
7. Click OK, Finish.

If this helps, please click the Yes button

Cheers,
Shane Devenshire
 
Top