Result based on two criteria
 

Hi,

I'm trying to set it up so that all figures which match the same two
criteria are all added up together, using only one formula.

Both criteria are based on Text - for instance "English", and "September"

So far I've got this for both, but can't work out how to combine them both.

=SUMIF(A:A,"English",G:G)
&
=SUMIF(B:B,"September",G:G)

Is there any way of getting it so that the overall sum of all entries
matching both criteria is shown?

Thanks in advance.

Result based on two criteria
 

Hi,
try

=SUMPRODUCT((A:A="English")*(B:B="September"),G:G)

"Ben12956" wrote:

Hi,

I'm trying to set it up so that all figures which match the same two
criteria are all added up together, using only one formula.

Both criteria are based on Text - for instance "English", and "September"

So far I've got this for both, but can't work out how to combine them both.

=SUMIF(A:A,"English",G:G)
&
=SUMIF(B:B,"September",G:G)

Is there any way of getting it so that the overall sum of all entries
matching both criteria is shown?

Thanks in advance.

Result based on two criteria
 

Try
=SUMPRODUCT((A1:A1000="English")*(B1:B1000="Septem ber"),G1:G1000)

--
Jacob


"Ben12956" wrote:

Hi,

I'm trying to set it up so that all figures which match the same two
criteria are all added up together, using only one formula.

Both criteria are based on Text - for instance "English", and "September"

So far I've got this for both, but can't work out how to combine them both.

=SUMIF(A:A,"English",G:G)
&
=SUMIF(B:B,"September",G:G)

Is there any way of getting it so that the overall sum of all entries
matching both criteria is shown?

Thanks in advance.

Result based on two criteria
 

If you're using Excel 2007:

=SUMIFS(G:G,A:A,"English",B:B,"September")

This one will work in any version of Excel but you *can't* use entire
columns as range references unless you're using Excel 2007.

=SUMPRODUCT(--(A1:A100="English"),--(B1:B100="September"),G1:G100)

Better to use cells to hold the criteria:

I1 = English
J1 = September

=SUMIFS(G:G,A:A,I1,B:B,J1)

=SUMPRODUCT(--(A1:A100=I1),--(B1:B100=J1),G1:G100)

--
Biff
Microsoft Excel MVP


"Ben12956" wrote in message
...
Hi,

I'm trying to set it up so that all figures which match the same two
criteria are all added up together, using only one formula.

Both criteria are based on Text - for instance "English", and "September"

So far I've got this for both, but can't work out how to combine them
both.

=SUMIF(A:A,"English",G:G)
&
=SUMIF(B:B,"September",G:G)

Is there any way of getting it so that the overall sum of all entries
matching both criteria is shown?

Thanks in advance.