Summation Across Multiple Criteria

[kyjae]

Hi All,

I've hit on a problem that's past my Excel abilities so I was hoping someone could point me in the right direction. In the attached file, I have 12 transactions recorded against two accounts, Account A and Account B. Two types of transaction can be made on both accounts, Transaction X and Transaction Y.

My first task is to count to the number of Transaction X's and Transaction Y's on Account A and the number of Transaction X's and Transaction Y's on Account B. This has been successfully done in cells G3, G4, H3 and H4 using the SUMPRODUCT formula.

However, I can't seem to find a solution to the next 6 requirements:-

Sum all of Transaction X's in £ Sterling (cell G7)
Sum all of Transaction Y's in £ Sterling (cell G8)
Sum all Transaction X's that occur on Account A in £ sterling (cell G10)
Sum all Transaction Y's that occur on Account A in £ sterling (cell G11)
Sum all Transaction X's that occur on Account B in £ sterling (cell G13)
Sum all Transaction Y's that occur on Account B in £ sterling (cell G14)

I've drawn a blank at trying to find a similar scenario via Google that could help me. Can anyone suggest how the above 6 unmet requirement can be achieved please?

Many thanks

[AlKey]

I would recommend to use matching criteria

Enter formula in G7 copy down to G8

=SUMIFS(D$3:D$14,C$3:C$14,H7)

then in G10 and copy down

Please Login or Register  to view this content.

v	F	G	H	I
2		No. Transaction X's	No. Transaction Y's
3	Account A:	4	3
4	Account B:	2	3
5
6
7	Total Transaction X's (£)	1445	Transaction X
8	Total Transaction Y's (£)	1435	Transaction Y
9
10	Total Transaction X's on Account A (£)	805	Transaction X	Account A
11	Total Transaction Y's on Account A (£)	235	Transaction Y	Account A
12
13	Total Transaction X's on Account B (£)	640	Transaction X	Account B
14	Total Transaction Y's on Account B (£)	1200	Transaction Y	Account B

[kyjae]

Hi AlKey,
The solution you suggested didn't work specifically on the file I supplied but it did introduce me to the SUMIFS function that I wasn't aware of before, so thank you for the right pointer.

[kyjae]

Hi AlKey,
The solution you suggested didn't work specifically on the file I supplied but it did introduce me to the SUMIFS function that I wasn't aware of before, so thank you for the right pointer.