Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query criteria based on a changing criteria list | Excel Discussion (Misc queries) | |||
Search 2 Criteria for 1 sep result | Excel Discussion (Misc queries) | |||
Result Based on Date Criteria | Excel Discussion (Misc queries) | |||
how can I have a formula result based on multiple criteria/columns | New Users to Excel | |||
vlookup based on random result returns incorrect result | Excel Worksheet Functions |