formula need but I do know if it is sumif or if and

[jenniferThomson]

Hello

I have very basic formula knowledge but require a formula to speed u
my day to day on this partiulcar spreadsheet.

A B
C
Client Process SQM

CEK Fibre 100
Land Gloss 150
Layman Pin 200
CEK Fibre 100

I want in another cell to add the sqm of all CEK Fibre ie the abov
answer is 200

Can anyone help me?

Thank you

Regards

je

 

[Norman Harker]

Hi Jennifer:

Try one way:

=SUMPRODUCT((A2:A5="CEK")*(B2:B5="Fibre")*(C2:C5))

(A2:A5="CEK") is an implicit IF statement that returns true or false.
Same with the second test
Sumproduct coerces these to 1 or 0 so you only add C2:C5 to the
product if both tests are true.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Paul]

Hello

I have very basic formula knowledge but require a formula to speed up
my day to day on this partiulcar spreadsheet.

A B
C
Client Process SQM

CEK Fibre 100
Land Gloss 150
Layman Pin 200
CEK Fibre 100

I want in another cell to add the sqm of all CEK Fibre ie the above
answer is 200

Can anyone help me?

Thank you

Regards

jen

Do you mean that you want to sum column C values where column A and column B
both meet their criteria? If so, use:
=SUMPRODUCT((A1:A100="CEK")*(B1:B100="Fibre")*(C1:C100))
Adjust the length of the ranges to suit your data, but keep them all the
same length (or make the range lengths longer than your data, as blank rows
will not affect the result).
Alternatively, you could put "CEK" and "Fibre" in cells, and their cell
references in the formula.