SUMIF?

[Jeff]

Thanks Frank that's great!

Is there a way Instead of typing the multiple criteria
into the formula, can I have them typed into a cell
already and just reference the cell?

If so what format would I need it to be?

 

[Bob Phillips]

Jeff,

Put the values in L1:N1 and use

=SUMPRODUCT((C1:C200=L1:N1)*(D1200))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

L1:N1? does this mean put them in a selection of reference
cells, but one in each?

 

[jeff]

L1:N1? does this mean put them in a selection of reference
cells, but one in each?

 

[Frank Kabel]

Hi Jeff
this is correct. One element per cell

 

[Guest]

Hi Frank, this is the exact formula.But it doesn't work
for some reason. shows #N/A.

=SUMPRODUCT((B5:B63=P4648)*(C5:C63))

These are the criteria in cells P4648.

1
8A
4/1

 

[Frank Kabel]

Hi
as Bob wrote: You have to put this in one ROW (e.g. P46:R46)
Or you may use the following array formula (entered with
CTRL+SHIFT+ENTER):
=SUMPRODUCT((B5:B63=TRANSPOSE(P4648))*(C5:C63))

 

[Bob Phillips]

Jeff,

This formula demands the comparison data to be in a row, so it should be
P46:R46. IF you must use P4648, you need to transpose it in the formula,
but this will need an array formula.

So use

=SUMPRODUCT((B5:B63=P46:R46))*(C5:C63))

as a normal formula, or

=SUMPRODUCT((B5:B63=TRANSPOSE(P4648))*(C5:C63))

as an array formula, so commit with Ctrl-Shift-Enter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)