Result based on two criteria

[Ben12956]

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.

 

[Eduardo]

Hi,
try

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

 

[Jacob Skaria]

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

 

[T. Valko]

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)