Hi
I am stumped by the following problem. I would like to calculate the summation of multiple non-adjacent columns of data that meet two conditions. For clarity, I'll refer to the mock dataset below.
A B C D ...
1 2 C 3
N 0 C 1
4 N I 2
3 N C 0
.
The first condition is that the value in Column C should be "C". The second condition is that string data should be treated as equal to 0. The desired formula should then give a value of 10.
Before the issue of string values cropped up, I was using sumproduct(--(C1:C4="C"),A1:A4+B1:B4+D1:D4), which was recommended to me by another user of this site. But for the above problem it returns the value error.
My guess is that this could be readily adapted to exclude string text in specified columns and I'd be interested if anyone knows such a solution. But I'd also be curious to see if some variation on the formula could be used so that specifying these 'culprit' columns wasn't necessary - this is useful for large datasets, for which creating multiple criteria may be arduous.
Kind regards
i_excel
I am stumped by the following problem. I would like to calculate the summation of multiple non-adjacent columns of data that meet two conditions. For clarity, I'll refer to the mock dataset below.
A B C D ...
1 2 C 3
N 0 C 1
4 N I 2
3 N C 0
.
The first condition is that the value in Column C should be "C". The second condition is that string data should be treated as equal to 0. The desired formula should then give a value of 10.
Before the issue of string values cropped up, I was using sumproduct(--(C1:C4="C"),A1:A4+B1:B4+D1:D4), which was recommended to me by another user of this site. But for the above problem it returns the value error.
My guess is that this could be readily adapted to exclude string text in specified columns and I'd be interested if anyone knows such a solution. But I'd also be curious to see if some variation on the formula could be used so that specifying these 'culprit' columns wasn't necessary - this is useful for large datasets, for which creating multiple criteria may be arduous.
Kind regards
i_excel